/*
 * OpenClinica is distributed under the
 * GNU Lesser General Public License (GNU LGPL).

 * For details see: http://www.openclinica.org/license
 * copyright 2003-2005 Akaza Research
 */
package core.org.akaza.openclinica.dao.extract;

import core.org.akaza.openclinica.bean.admin.CRFBean;
import core.org.akaza.openclinica.bean.core.*;
import core.org.akaza.openclinica.bean.extract.DatasetBean;
import core.org.akaza.openclinica.bean.managestudy.*;
import core.org.akaza.openclinica.bean.odmbeans.*;
import core.org.akaza.openclinica.bean.odmbeans.DiscrepancyNoteBean;
import core.org.akaza.openclinica.bean.service.StudyParameterValueBean;
import core.org.akaza.openclinica.bean.submit.CRFVersionBean;
import core.org.akaza.openclinica.bean.submit.EventCRFBean;
import core.org.akaza.openclinica.bean.submit.FormLayoutBean;
import core.org.akaza.openclinica.bean.submit.SectionBean;
import core.org.akaza.openclinica.bean.submit.crfdata.*;
import core.org.akaza.openclinica.core.form.StringUtil;
import core.org.akaza.openclinica.dao.admin.CRFDAO;
import core.org.akaza.openclinica.dao.core.CoreResources;
import core.org.akaza.openclinica.dao.core.SQLFactory;
import core.org.akaza.openclinica.dao.core.TypeNames;
import core.org.akaza.openclinica.dao.hibernate.StudyDao;
import core.org.akaza.openclinica.dao.managestudy.*;
import core.org.akaza.openclinica.dao.service.StudyParameterValueDAO;
import core.org.akaza.openclinica.dao.submit.CRFVersionDAO;
import core.org.akaza.openclinica.dao.submit.EventCRFDAO;
import core.org.akaza.openclinica.dao.submit.FormLayoutDAO;
import core.org.akaza.openclinica.dao.submit.SectionDAO;
import core.org.akaza.openclinica.domain.SourceDataVerification;

import core.org.akaza.openclinica.domain.datamap.Study;
import core.org.akaza.openclinica.i18n.util.ResourceBundleProvider;
import core.org.akaza.openclinica.job.JobTerminationMonitor;
import core.org.akaza.openclinica.logic.odmExport.ClinicalDataCollector;
import core.org.akaza.openclinica.logic.odmExport.ClinicalDataUtil;
import core.org.akaza.openclinica.logic.odmExport.MetadataUnit;
import core.org.akaza.openclinica.service.extract.GenerateClinicalDataService;
import core.org.akaza.openclinica.service.managestudy.EventDefinitionCrfTagService;
import org.akaza.openclinica.domain.enumsupport.EventCrfWorkflowStatusEnum;
import org.akaza.openclinica.domain.enumsupport.SdvStatus;
import org.akaza.openclinica.domain.enumsupport.StudyEventWorkflowStatusEnum;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;

import javax.sql.DataSource;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Fetch odm data from database and load odm related classes.
 * @author ywang (May, 2008)
 */

public class OdmExtractDAO extends DatasetDAO {
    EventDefinitionCRFDAO edcdao = new EventDefinitionCRFDAO(ds);
    StudyEventDefinitionDAO seddao = new StudyEventDefinitionDAO(ds);
    CRFVersionDAO cvdao = new CRFVersionDAO<>(ds);
    CRFDAO crfdao = new CRFDAO(ds);
    FormLayoutDAO fldao = new FormLayoutDAO(ds);
    EventDefinitionCrfTagDAO edctdao = new EventDefinitionCrfTagDAO(ds);
    StudyEventDAO sedao = new StudyEventDAO(ds);
    EventCRFDAO ecdao = new EventCRFDAO(ds);
    Set<Integer> edcSet;

    private StudyDao studyDao;
    private final Integer MAX_STRING_LENGTH = 4000;

    EventDefinitionCrfTagService eventDefinitionCrfTagService;
    protected boolean showArchived;

    public OdmExtractDAO(DataSource ds, StudyDao studyDao) {
        super(ds);
        this.studyDao = studyDao;
    }

    public OdmExtractDAO(DataSource ds, Set<Integer> edcSet, StudyDao studyDao) {
        super(ds);
        this.edcSet = edcSet;
        this.studyDao = studyDao;
    }

    public OdmExtractDAO(DataSource ds, boolean showArchived, StudyDao studyDao) {
        super(ds);
        this.showArchived = showArchived;
        this.studyDao = studyDao;
    }

    @Override
    protected void setDigesterName() {
        digesterName = SQLFactory.getInstance().DAO_ODM_EXTRACT;
    }

    public void setStudyGroupClassTypesExpected() {
        this.unsetTypeExpected();
        this.setTypeExpected(1, TypeNames.INT);// study_group_class_id
        this.setTypeExpected(2, TypeNames.STRING);// sgc_name(study_group_class
        // name)
        this.setTypeExpected(3, TypeNames.STRING);// sgc_type(group_class_type
        // name)
        this.setTypeExpected(4, TypeNames.INT);// (study_group_class) status_id
        this.setTypeExpected(5, TypeNames.STRING);// subject_assignment
        this.setTypeExpected(6, TypeNames.INT);// study_group_id
        this.setTypeExpected(7, TypeNames.STRING);// (study_group) sg_name
        this.setTypeExpected(8, TypeNames.STRING);// study_group description
    }

    public void setStudyEventAndFormMetaTypesExpected() {
        this.unsetTypeExpected();
        this.setTypeExpected(1, TypeNames.INT);// definition_order
        this.setTypeExpected(2, TypeNames.INT); // crf_order
        this.setTypeExpected(3, TypeNames.INT); // edc.crf_id
        this.setTypeExpected(4, TypeNames.INT); // cv.crf_version_id
        this.setTypeExpected(5, TypeNames.STRING);// definition_oid
        this.setTypeExpected(6, TypeNames.STRING);// definition_name
        this.setTypeExpected(7, TypeNames.BOOL);// definition_repeating
        this.setTypeExpected(8, TypeNames.STRING);// definition_type
        this.setTypeExpected(9, TypeNames.STRING);// cv_oid
        this.setTypeExpected(10, TypeNames.STRING);// cv_name
        this.setTypeExpected(11, TypeNames.BOOL);// cv_required
        this.setTypeExpected(12, TypeNames.STRING);// null_values
        this.setTypeExpected(13, TypeNames.STRING);// crf_name
        this.setTypeExpected(14, TypeNames.STRING);// crf_oid
        this.setTypeExpected(15, TypeNames.INT);// sed_status_id
        this.setTypeExpected(16, TypeNames.INT); // edc_status_id
    }

    public void setStudyEventAndFormMetaOC1_3TypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT);// definition_order
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // crf_order
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // edc.crf_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // cv.crf_version_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// definition_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// cv_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// description
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// category
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// version_description
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// revision_notes
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// crf_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// crf_description
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// null_values
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // default_version_id
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL); // electronic_signature
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL);// double_entry
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL);// hide_crf
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL);// participant_form
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL);// allow_anonymous_submission
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// submission_url
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL);// offline
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// source_data_verification_code
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// sed.status_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// edc.status_id
    }

    public void setItemDataMaxLengthTypesExpected() {
        this.unsetTypeExpected();
        this.setTypeExpected(1, TypeNames.INT);// item_id
        this.setTypeExpected(2, TypeNames.INT);// max_length
    }

    public void setItemGroupAndItemMetaWithUnitTypesExpected() {
        this.unsetTypeExpected();
        this.setTypeExpected(1, TypeNames.INT);// crf_id
        this.setTypeExpected(2, TypeNames.INT);// crf_version_id
        this.setTypeExpected(3, TypeNames.INT);// item_group_id
        this.setTypeExpected(4, TypeNames.INT);// item_id
        this.setTypeExpected(5, TypeNames.INT);// response_set_id
        this.setTypeExpected(6, TypeNames.STRING);// crf_version_oid
        this.setTypeExpected(7, TypeNames.STRING);// item_group_oid
        this.setTypeExpected(8, TypeNames.STRING);// item_oid
        this.setTypeExpected(9, TypeNames.STRING); // item_group_name
        this.setTypeExpected(10, TypeNames.STRING);// item_name
        this.setTypeExpected(11, TypeNames.INT);// item_data_type_id
        this.setTypeExpected(12, TypeNames.STRING);// item_header
        this.setTypeExpected(13, TypeNames.STRING);// left_item_text
        this.setTypeExpected(14, TypeNames.STRING);// right_item_text
        this.setTypeExpected(15, TypeNames.BOOL);// item_required
        this.setTypeExpected(16, TypeNames.STRING);// regexp
        this.setTypeExpected(17, TypeNames.STRING);// regexp_error_msg
        this.setTypeExpected(18, TypeNames.STRING);// width_decimal
        this.setTypeExpected(19, TypeNames.INT);// response_type_id
        this.setTypeExpected(20, TypeNames.STRING);// options_text
        this.setTypeExpected(21, TypeNames.STRING);// options_values
        this.setTypeExpected(22, TypeNames.STRING);// response_label
        this.setTypeExpected(23, TypeNames.STRING);// item_group_header
        this.setTypeExpected(24, TypeNames.BOOL);// is Repeating?
        this.setTypeExpected(25, TypeNames.STRING);// item_description
        this.setTypeExpected(26, TypeNames.STRING);// item_brief_description
        this.setTypeExpected(27, TypeNames.INT);// section_id
        this.setTypeExpected(28, TypeNames.STRING); // question_number_label
        this.setTypeExpected(29, TypeNames.STRING);// mu_oid
    }

    public void setItemGroupAndItemMetaOC1_3TypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT);// crf_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// crf_version_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// item_group_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// item_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// response_set_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// crf_version_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// item_group_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// item_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// item_header
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// subheader
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// section_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// left_item_text
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// right_item_text
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// parent_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// column_number
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// page_number_label
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// response_layout
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// default_value
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL);// phi_status
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL);// show_item
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// response_type_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// repeat_number
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// repeat_max
        ++i;
        this.setTypeExpected(i, TypeNames.BOOL);// show_group
        ++i;
        this.setTypeExpected(i, TypeNames.INT);// item_order
        ++i;
        this.setTypeExpected(i, TypeNames.STRING);// crf_version_oid
    }

    public void setSubjectEventFormDataTypesExpected() {
        this.unsetTypeExpected();
        this.setTypeExpected(1, TypeNames.STRING);// study_subject_oid;
        this.setTypeExpected(2, TypeNames.INT);// definition_order;
        this.setTypeExpected(3, TypeNames.STRING);// definition_oid;
        this.setTypeExpected(4, TypeNames.BOOL);// definition_repeating
        this.setTypeExpected(5, TypeNames.INT);// sample_ordinal
        this.setTypeExpected(6, TypeNames.INT);// crf_order;
        this.setTypeExpected(7, TypeNames.STRING);// crf_version_oid
        this.setTypeExpected(8, TypeNames.INT);// event_crf_id
    }

    public void setSubjectEventFormDataTypesExpected(String odmVersion) {
        if (odmVersion.equalsIgnoreCase("occlinical_data"))
            odmVersion = "oc1.3";
        if ("1.2".equals(odmVersion) || "1.3".equals(odmVersion)) {
            setSubjectEventFormDataTypesExpected();
        } else if ("oc1.2".equals(odmVersion) || "oc1.3".equals(odmVersion)) {
            this.unsetTypeExpected();
            this.setTypeExpected(1, TypeNames.STRING);// study_subject_oid;
            this.setTypeExpected(2, TypeNames.STRING);// label(study subject)
            this.setTypeExpected(3, TypeNames.STRING);// unique-identifier
            this.setTypeExpected(4, TypeNames.STRING);// secondary_label(study
            // subject)
            this.setTypeExpected(5, TypeNames.STRING);// gender
            this.setTypeExpected(6, TypeNames.DATE);// date_of_birth
            this.setTypeExpected(7, TypeNames.INT);// status_id (subject)
            this.setTypeExpected(8, TypeNames.INT);// sgc_id
            this.setTypeExpected(9, TypeNames.STRING);// sgc_name
            this.setTypeExpected(10, TypeNames.STRING);// sg_name
            this.setTypeExpected(11, TypeNames.INT);// definition_order;
            this.setTypeExpected(12, TypeNames.STRING);// definition_oid;
            this.setTypeExpected(13, TypeNames.BOOL);// definition_repeating
            this.setTypeExpected(14, TypeNames.INT);// sample_ordinal
            this.setTypeExpected(15, TypeNames.STRING);// se_location (study
            // event)
            this.setTypeExpected(16, TypeNames.DATE);// date_start (study
            // event)
            this.setTypeExpected(17, TypeNames.DATE);// date_end (study
            // event)
            this.setTypeExpected(18, TypeNames.BOOL);// start_time_flag
            this.setTypeExpected(19, TypeNames.BOOL);// end_time_flag
            this.setTypeExpected(20, TypeNames.STRING);// event_workflow_status

            this.setTypeExpected(21, TypeNames.BOOL);// event_removed
            this.setTypeExpected(22, TypeNames.BOOL);// event_archived
            this.setTypeExpected(23, TypeNames.BOOL);// event_locked
            this.setTypeExpected(24, TypeNames.BOOL);// event_signed

            this.setTypeExpected(25, TypeNames.INT);// crf_order;
            this.setTypeExpected(26, TypeNames.INT);// edc_id;
            this.setTypeExpected(27, TypeNames.STRING);// crf_version_oid
            this.setTypeExpected(28, TypeNames.STRING);// crf_version
            this.setTypeExpected(29, TypeNames.INT); // cv_status_id
            this.setTypeExpected(30, TypeNames.STRING);//  event_crf_workflow_status

            this.setTypeExpected(31, TypeNames.BOOL);// form_removed
            this.setTypeExpected(32, TypeNames.BOOL);// form_archived

            this.setTypeExpected(33, TypeNames.INT);// event_crf_id
            this.setTypeExpected(34, TypeNames.DATE);// date_interviewed
            this.setTypeExpected(35, TypeNames.STRING);// interviewer_name
            this.setTypeExpected(36, TypeNames.INT);// validator_id
            this.setTypeExpected(37, TypeNames.STRING);// definition_name;
        }
    }

    public void setEventGroupItemDataWithUnitTypesExpected() {
        this.unsetTypeExpected();
        this.setTypeExpected(1, TypeNames.INT);// event_crf_id;
        this.setTypeExpected(2, TypeNames.INT);// item_group_id;
        this.setTypeExpected(3, TypeNames.STRING);// item_group_oid
        this.setTypeExpected(4, TypeNames.STRING);// item_group_name
        this.setTypeExpected(5, TypeNames.INT);// item_id
        this.setTypeExpected(6, TypeNames.STRING);// item_oid
        this.setTypeExpected(7, TypeNames.INT);// item_data_ordinal
        this.setTypeExpected(8, TypeNames.STRING);// value
        this.setTypeExpected(9, TypeNames.INT);// item_data_type_id
        this.setTypeExpected(10, TypeNames.INT);// item_data_id
        this.setTypeExpected(11, TypeNames.STRING);// item_name
        this.setTypeExpected(12, TypeNames.STRING);// mu_oid
    }

    public void setEventCrfIdsByItemDataTypesExpected() {
        this.unsetTypeExpected();
        this.setTypeExpected(1, TypeNames.INT);// event_crf_id;
    }

    public void setStudyMeasurementUnitsTypesExpected() {
        // select distinct mu.oc_oid as mu_oid, mu.name
        // mus.text
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.STRING); // mu_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // name
    }

    public void setNullValueCVsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;

        this.setTypeExpected(i, TypeNames.STRING); // study_event_definition oc_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // crf_version oc_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // null_values
    }

    public void setItemCVOIDsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // crf_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // crf_version_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // item_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // cv_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // item_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // crf_oid
    }

    public void setStudyUsersTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // user_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // first_name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // last_name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // institution_affilition
    }

    public void setOCSubjectDataAuditsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.STRING); // study_subject_oid
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // audit_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // name
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // user_id
        ++i;
        this.setTypeExpected(i, TypeNames.TIMESTAMP); // audit_date
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // reason_for_change
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // old_value
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // new_value
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // audit_log_event_type_id
    }

    public void setOCEventDataAuditsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.STRING); // study_subject_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // definition_oid
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // audit_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // event_repeat
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // name
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // user_id
        ++i;
        this.setTypeExpected(i, TypeNames.TIMESTAMP); // audit_date
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // audit_entity_name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // reason_for_change
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // old_value
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // new_value
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // details
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // audit_log_event_type_id
    }

    public void setOCFormDataAuditsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // event_crf_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // audit_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // name
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // user_id
        ++i;
        this.setTypeExpected(i, TypeNames.TIMESTAMP); // audit_date
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // audit_entity_name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // reason_for_change
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // old_value
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // new_value
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // audit_log_event_type_id
    }

    public void setOCItemDataAuditsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // item_data_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // audit_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // name
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // user_id
        ++i;
        this.setTypeExpected(i, TypeNames.TIMESTAMP); // audit_date
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // reason_for_change
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // old_value
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // new_value
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // audit_log_event_type_id
    }

    public void setOCSubjectDataDNsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.STRING); // study_subject_oid
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // parent_dn_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // dn_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // description
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // detailed_notes
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // owner_id
        ++i;
        this.setTypeExpected(i, TypeNames.TIMESTAMP); // date_created
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // status
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // discrepancy_note_type.name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // discrepancy_note.thread_uuid
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // discrepancy_note.thread_number
    }

    public void setOCEventDataDNsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.STRING); // study_subject_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // definition_oid
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // parent_dn_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // dn_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // description
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // detailed_notes
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // owner_id
        ++i;
        this.setTypeExpected(i, TypeNames.TIMESTAMP); // date_created
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // status
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // discrepancy_note_type.name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // discrepancy_note.thread_uuid
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // discrepancy_note.thread_number
    }

    public void setOCFormDataDNsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // event_crf_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // parent_dn_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // dn_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // description
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // detailed_notes
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // owner_id
        ++i;
        this.setTypeExpected(i, TypeNames.TIMESTAMP); // date_created
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // status
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // discrepancy_note_type.name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // discrepancy_note.thread_uuid
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // discrepancy_note.thread_number
    }

    public void setOCItemDataDNsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // item_data_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // parent_dn_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // dn_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // description
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // detailed_notes
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // owner_id
        ++i;
        this.setTypeExpected(i, TypeNames.TIMESTAMP); // date_created
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // status
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // discrepancy_note_type.name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // discrepancy_note.thread_uuid
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // discrepancy_note.thread_number
    }

    public void setSectionLabelsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // section_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // label
    }

    public void setParentItemOIDsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // item_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // item oc_oid
    }

    public void setSCDsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // crf_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // crf_version_id
        ++i;
        this.setTypeExpected(i, TypeNames.INT); // item_id
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // crf_version_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // item_oid
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // control_item_name
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // option_value
        ++i;
        this.setTypeExpected(i, TypeNames.STRING); // message
    }

    public void setErasedScoreItemDataIdsTypesExpected() {
        this.unsetTypeExpected();
        int i = 1;
        this.setTypeExpected(i, TypeNames.INT); // item_data_id
    }

    public void getBasicDefinitions(int studyId, BasicDefinitionsBean basicDef) {
        ArrayList<MeasurementUnitBean> units = basicDef.getMeasurementUnits();
        String uprev = "";
        this.setStudyMeasurementUnitsTypesExpected();
        ArrayList rows = this.select(this.getStudyMeasurementUnitsSql(studyId));
        Iterator it = rows.iterator();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            String oid = (String) row.get("mu_oid");
            String name = (String) row.get("name");
            MeasurementUnitBean u = new MeasurementUnitBean();
            SymbolBean symbol = new SymbolBean();
            ArrayList<TranslatedTextBean> texts = new ArrayList<TranslatedTextBean>();
            if (uprev.equals(oid)) {
                u = units.get(units.size() - 1);
                symbol = u.getSymbol();
                texts = symbol.getTranslatedText();
            } else {
                u.setOid(oid);
                u.setName(name);
                units.add(u);
            }
            TranslatedTextBean t = new TranslatedTextBean();
            t.setText(name);
            texts.add(t);
            symbol.setTranslatedText(texts);
            u.setSymbol(symbol);
        }
    }

    public void getBasicDefinitions(String crfVersionOID, BasicDefinitionsBean basicDef) {
        ArrayList<MeasurementUnitBean> units = basicDef.getMeasurementUnits();
        String uprev = "";
        this.setStudyMeasurementUnitsTypesExpected();
        ArrayList rows = this.select(this.getStudyMeasurementUnitsSql(crfVersionOID));
        Iterator it = rows.iterator();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            String oid = (String) row.get("mu_oid");
            String name = (String) row.get("name");
            MeasurementUnitBean u = new MeasurementUnitBean();
            SymbolBean symbol = new SymbolBean();
            ArrayList<TranslatedTextBean> texts = new ArrayList<TranslatedTextBean>();
            if (uprev.equals(oid)) {
                u = units.get(units.size() - 1);
                symbol = u.getSymbol();
                texts = symbol.getTranslatedText();
            } else {
                u.setOid(oid);
                u.setName(name);
                units.add(u);
            }
            TranslatedTextBean t = new TranslatedTextBean();
            t.setText(name);
            texts.add(t);
            symbol.setTranslatedText(texts);
            u.setSymbol(symbol);
        }
    }

    public void getUpdatedSiteMetadata(int parentStudyId, int studyId, MetaDataVersionBean metadata, String odmVersion, String permissionTagsString) {
        HashMap<Integer, Integer> cvIdPoses = new HashMap<Integer, Integer>();

        this.setStudyEventAndFormMetaTypesExpected();
        ArrayList rows = this.select(this.getStudyEventAndFormMetaSql(parentStudyId, studyId, true, showArchivedSql(showArchived)));
        Iterator it = rows.iterator();
        String sedprev = "";
        MetaDataVersionProtocolBean protocol = metadata.getProtocol();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            Integer sedOrder = (Integer) row.get("definition_order");
            Integer cvId = (Integer) row.get("crf_version_id");
            String sedOID = (String) row.get("definition_oid");
            String sedName = (String) row.get("definition_name");
            Boolean sedRepeat = (Boolean) row.get("definition_repeating");
            String sedType = (String) row.get("definition_type");
            String cvOID = (String) row.get("cv_oid");
            String cvName = (String) row.get("cv_name");
            Boolean cvRequired = (Boolean) row.get("cv_required");
            String nullValue = (String) row.get("null_values");
            String crfName = (String) row.get("crf_name");
            String crfOid = (String) row.get("crf_oid");
            Integer sedStatusId = (Integer) row.get("sed_status_id");
            Integer edcStatusId = (Integer) row.get("edc_status_id");

            StudyEventDefBean sedef = new StudyEventDefBean();
            if (sedprev.equals(sedOID)) {
                int p = metadata.getStudyEventDefs().size() - 1;
                sedef = metadata.getStudyEventDefs().get(p);
            } else {
                sedprev = sedOID;
                ElementRefBean sedref = new ElementRefBean();
                sedref.setElementDefOID(sedOID);
                sedref.setMandatory("Yes");
                sedref.setOrderNumber(sedOrder);
                protocol.getStudyEventRefs().add(sedref);
                sedef.setOid(sedOID);
                sedef.setName(sedName);
                sedef.setRepeating(sedRepeat ? "Yes" : "No");
                sedef.setStatus(core.org.akaza.openclinica.domain.Status.getByCode(sedStatusId).getName());
                String type = sedType.toLowerCase();
                type = type.substring(0, 1).toUpperCase() + type.substring(1);
                sedef.setType(type);
                metadata.getStudyEventDefs().add(sedef);
            }

            ElementRefBean formref = new ElementRefBean();
            formref.setElementDefOID(crfOid);
            formref.setMandatory(cvRequired ? "Yes" : "No");
            formref.setStatus(core.org.akaza.openclinica.domain.Status.getByCode(edcStatusId).getName());
            sedef.getFormRefs().add(formref);

            if (!cvIdPoses.containsKey(cvId)) {
                FormDefBean formdef = new FormDefBean();
                formdef.setOid(crfOid);
                formdef.setName(crfName);
                formdef.setRepeating("No");
                metadata.getFormDefs().add(formdef);
                cvIdPoses.put(cvId, metadata.getFormDefs().size() - 1);
                if (nullValue != null && nullValue.length() > 0) {
                }
            }
        }
    }

    public void getMetadata(Study parentStudy, Study study, MetaDataVersionBean metadata, String odmVersion) {
        if (odmVersion.equalsIgnoreCase("occlinical_data"))
            odmVersion = "oc1.3";

        if ("oc1.3".equals(odmVersion)) {
            // this.getOCMetadata(parentStudyId, study, metadata, odmVersion);
            this.getMetadataOC1_3(parentStudy, study, metadata, odmVersion);
        } else if ("oc1.2".equals(odmVersion)) {
            this.getOCMetadata(parentStudy.getStudyId(), study.getStudyId(), metadata, odmVersion);
        } else {
            this.getODMMetadata(parentStudy.getStudyId(), study.getStudyId(), metadata, odmVersion);
        }
    }

    /**
     * Metadata to be obtained based on formVersionOID. The studyOID and studyEventOID are irrelevant as these are the
     * global crfs, i.e crfs that do not belong to
     * any particular CRF. In order to comply with cdisc ODM, the studyOID, studyeventDefOID etc will be static values
     * which do not exist in the database.
     * @param metadata
     */

    // The method underneath tries to reuse the code based on getODMMetadata
    public void getODMMetadataForForm(MetaDataVersionBean metadata, String formOID, String odmVersion) {
        FormDefBean formDef = new FormDefBean();
        String cvIds = new String("");
        CRFBean crfBean = crfdao.findByOid(formOID);
        List<CRFVersionBean> crfVersions = (List<CRFVersionBean>) cvdao.findAllActiveByCRF(crfBean.getId());
        CRFVersionBean crfVersionBean = crfVersions.get(0);

        cvIds = crfVersionBean.getId() + "";

        applyStudyEventDef(metadata, formOID);

        fetchItemGroupMetaData(metadata, cvIds, odmVersion);
        getOCMetadataForGlobals(crfVersionBean.getId(), metadata, odmVersion);
        ArrayList rows = new ArrayList();
        ArrayList<ItemGroupDefBean> igs = (ArrayList<ItemGroupDefBean>) metadata.getItemGroupDefs();
        HashMap<String, Integer> igPoses = getItemGroupOIDPos(metadata);
        ArrayList<ItemDefBean> its = (ArrayList<ItemDefBean>) metadata.getItemDefs();

        HashMap<String, Integer> itPoses = getItemOIDPos(metadata);
        HashMap<String, Integer> inPoses = new HashMap<String, Integer>();
        ItemGroupDefBean ig = new ItemGroupDefBean();
        Iterator it;

        metadata.setCvIds(cvIds);

/*        HashMap<Integer, Integer> maxLengths = new HashMap<Integer, Integer>();
        this.setItemDataMaxLengthTypesExpected();
        rows.clear();
        logger.debug("Begin to execute GetItemDataMaxLengths");
        rows = select(this.getItemDataMaxLengths(cvIds));
        it = rows.iterator();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            maxLengths.put((Integer) row.get("item_id"), (Integer) row.get("max_length"));
        }*/

        ItemDefBean itDef = new ItemDefBean();
        formDef = fetchFormDetails(crfBean, formDef);
        this.setItemGroupAndItemMetaWithUnitTypesExpected();
        rows.clear();
        String prevCvIg = "";

        logger.debug("Begin to execute GetItemGroupAndItemMetaWithUnitSql");
        logger.debug("getItemGroupandItemMetaWithUnitsql= " + this.getItemGroupAndItemMetaWithUnitSql(cvIds));
        HashMap<Integer, String> sectionLabels = this.getSectionLabels(metadata.getSectionIds());
        HashMap<Integer, String> parentItemOIDs = this.getParentItemOIDs(cvIds);
        this.setItemGroupAndItemMetaOC1_3TypesExpected();
        logger.debug("Begin to execute GetItemGroupAndItemMetaWithUnitSql");
        logger.debug("getItemGroupandItemMetaWithUnitsql= " + this.getItemGroupAndItemMetaOC1_3Sql(cvIds));
        rows = select(this.getItemGroupAndItemMetaOC1_3Sql(cvIds));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            Integer cvId = (Integer) row.get("crf_version_id");
            Integer igId = (Integer) row.get("item_group_id");
            String cvOID = (String) row.get("crf_version_oid");
            String igOID = (String) row.get("item_group_oid");
            String itOID = (String) row.get("item_oid");

            Integer igRepeatNum = (Integer) row.get("repeat_number");
            Integer igRepeatMax = (Integer) row.get("repeat_max");
            Boolean showGroup = (Boolean) row.get("show_group");
            String itemGroupHeader = (String) row.get("item_group_header");

            String itHeader = (String) row.get("item_header");
            String left = (String) row.get("left_item_text");
            String right = (String) row.get("right_item_text");
            String itSubheader = (String) row.get("subheader");
            Integer itSecId = (Integer) row.get("section_id");
            Integer itPId = (Integer) row.get("parent_id");
            Integer itColNum = (Integer) row.get("column_number");
            String itpgNum = (String) row.get("page_number_label");
            String layout = (String) row.get("response_layout");
            Integer rsTypeId = (Integer) row.get("response_type_id");
            String dfValue = (String) row.get("default_value");
            Boolean phi = (Boolean) row.get("phi_status");
            Boolean showItem = (Boolean) row.get("show_item");
            Integer orderInForm = (Integer) row.get("item_order");
            if ((cvId + "-" + igId).equals(prevCvIg)) {
            } else {
                prevCvIg = cvId + "-" + igId;
                ig = igs.get(igPoses.get(igOID));
                ItemGroupDetailsBean igDetail = ig.getItemGroupDetails();
                igDetail.setOid(igOID);

                PresentInFormBean inForm = new PresentInFormBean();
                inForm.setFormOid(cvOID);
                ItemGroupRepeatBean igr = new ItemGroupRepeatBean();

                igr.setRepeatMax(igRepeatMax);
                igr.setRepeatNumber(igRepeatNum);
                inForm.setItemGroupRepeatBean(igr);
                inForm.setShowGroup(showGroup == true ? "Yes" : "No");
                inForm.setItemGroupHeader(itemGroupHeader);
                igDetail.getPresentInForms().add(inForm);
                ig.setItemGroupDetails(igDetail);

            }
            itDef = its.get(itPoses.get(itOID));
            ItemDetailsBean itDetail = itDef.getItemDetails();
            itDetail.setOid(itOID);
            ItemPresentInFormBean itInForm = new ItemPresentInFormBean();
            itInForm.setFormOid(cvOID);
            itInForm.setColumnNumber(itColNum);
            itInForm.setDefaultValue(dfValue);
            itInForm.setItemHeader(itHeader);
            itInForm.setLeftItemText(left);
            itInForm.setRightItemText(right);
            itInForm.setItemSubHeader(itSubheader);
            itInForm.setPageNumber(itpgNum);
            itInForm.setParentItemOid(parentItemOIDs.get(itPId));
            itInForm.setSectionLabel(sectionLabels.get(itSecId));
            itInForm.setPhi(phi == false ? "No" : "Yes");
            itInForm.setOrderInForm(orderInForm);
            itInForm.setShowItem(showItem == true ? "Yes" : "No");
            ItemResponseBean itemResponse = new ItemResponseBean();
            itemResponse.setResponseLayout(layout);
            itemResponse.setResponseType(ResponseType.get(rsTypeId).getName());
            itInForm.setItemResponse(itemResponse);
            itDetail.getItemPresentInForm().add(itInForm);
            inPoses.put(itOID + "-" + cvOID, itDetail.getItemPresentInForm().size() - 1);
        }
        this.getSCDs(cvIds, its, itPoses, inPoses);

        metadata.getFormDefs().add(formDef);
    }

    private void applyStudyEventDef(MetaDataVersionBean metadata, String formOID) {

        StudyEventDefBean studyEventDef = new StudyEventDefBean();
        studyEventDef.setOid(MetadataUnit.FAKE_STUDY_EVENT_OID);
        studyEventDef.setName(MetadataUnit.FAKE_SE_NAME);
        studyEventDef.setRepeating(MetadataUnit.FAKE_SE_REPEATING);

        ElementRefBean formRefBean = new ElementRefBean();
        formRefBean.setElementDefOID(formOID);
        studyEventDef.getFormRefs().add(formRefBean);

        metadata.getStudyEventDefs().add(studyEventDef);

    }

    public FormDefBean fetchFormDetails(CRFBean crfBean, FormDefBean formDef) {

        formDef.setOid(crfBean.getOid());
        formDef.setName(crfBean.getName());

        List<ElementRefBean> formLayoutRefs = getFormLayoutRefs(formDef);
        formDef.setFormLayoutRefs(formLayoutRefs);
        formDef.setRepeating("No");
        FormDetailsBean formDetails = new FormDetailsBean();
        formDetails.setName(crfBean.getName());
        formDetails.setOid(crfBean.getOid());
        formDetails.setParentFormOid(crfBean.getOid());
        formDetails.setDescription(crfBean.getDescription());

        setSectionBean(formDetails, new Integer(crfBean.getId()));

        formDef.setFormDetails(formDetails);

        return formDef;
    }

    /**
     * metadata for ODM extraction only
     */
    public void getODMMetadata(int parentStudyId, int studyId, MetaDataVersionBean metadata, String odmVersion) {
        String cvIds = "";
        HashMap<Integer, Integer> cvIdPoses = new HashMap<Integer, Integer>();
        this.setStudyEventAndFormMetaTypesExpected();

        logger.debug("Begin to execute GetStudyEventAndFormMetaSql");
        logger.info("getStudyEventAndFormMetaSQl= " + this.getStudyEventAndFormMetaSql(parentStudyId, studyId, false, showArchivedSql(showArchived)));
        ArrayList rows = this.select(this.getStudyEventAndFormMetaSql(parentStudyId, studyId, false, showArchivedSql(showArchived)));
        Iterator it = rows.iterator();
        String sedprev = "";
        MetaDataVersionProtocolBean protocol = metadata.getProtocol();
        HashMap<Integer, String> nullMap = new HashMap<Integer, String>();
        HashMap<String, String> nullValueCVs = new HashMap<String, String>();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            Integer sedOrder = (Integer) row.get("definition_order");
            Integer cvId = (Integer) row.get("crf_version_id");
            String sedOID = (String) row.get("definition_oid");
            String sedName = (String) row.get("definition_name");
            Boolean sedRepeat = (Boolean) row.get("definition_repeating");
            String sedType = (String) row.get("definition_type");
            String cvOID = (String) row.get("cv_oid");
            String cvName = (String) row.get("cv_name");
            Boolean cvRequired = (Boolean) row.get("cv_required");
            String nullValue = (String) row.get("null_values");
            String crfName = (String) row.get("crf_name");
            String crfOid = (String) row.get("crf_oid");
            Integer sedStatusId = (Integer) row.get("sed_status_id");
            Integer edcStatusId = (Integer) row.get("edc_status_id");

            StudyEventDefBean sedef = new StudyEventDefBean();
            if (sedprev.equals(sedOID)) {
                int p = metadata.getStudyEventDefs().size() - 1;
                sedef = metadata.getStudyEventDefs().get(p);
            } else {
                sedprev = sedOID;
                ElementRefBean sedref = new ElementRefBean();
                sedref.setElementDefOID(sedOID);
                sedref.setMandatory("Yes");
                sedref.setOrderNumber(sedOrder);
                protocol.getStudyEventRefs().add(sedref);
                sedef.setOid(sedOID);
                sedef.setName(sedName);
                sedef.setRepeating(sedRepeat ? "Yes" : "No");
                sedef.setStatus(core.org.akaza.openclinica.domain.Status.getByCode(sedStatusId).getName());
                String type = sedType.toLowerCase();
                type = type.substring(0, 1).toUpperCase() + type.substring(1);
                sedef.setType(type);
                metadata.getStudyEventDefs().add(sedef);
            }

            ElementRefBean formref = new ElementRefBean();
            formref.setElementDefOID(crfOid);
            formref.setMandatory(cvRequired ? "Yes" : "No");
            formref.setStatus(core.org.akaza.openclinica.domain.Status.getByCode(edcStatusId).getName());
            sedef.getFormRefs().add(formref);

            if (!cvIdPoses.containsKey(cvId)) {
                FormDefBean formdef = new FormDefBean();
                formdef.setOid(crfOid);
                formdef.setName(crfName);
                formdef.setRepeating("No");
                metadata.getFormDefs().add(formdef);
                cvIdPoses.put(cvId, metadata.getFormDefs().size() - 1);
                cvIds += cvId + ",";
                if (nullValue != null && nullValue.length() > 0) {
                    nullMap.put(cvId, nullValue);
                    nullValueCVs.put(sedOID + "-" + cvOID, nullValue);
                }
            }
        }
        cvIds = cvIds.length() > 0 ? cvIds.substring(0, cvIds.length() - 1) : "";

        metadata.setCvIds(cvIds);

        // This max lengths field which is displayed in the item definition bean was calculated dynamically.
        // In studies with larges amounts of item data this call could take upwards of 30-60+ seconds.
        // Because knowing the max length of a text field is not very useful we have decided to simply
        // hardcode this value in. The maximum value for this field should be 4000 characters as specified
        // in the database.
/*        HashMap<Integer, Integer> maxLengths = new HashMap<Integer, Integer>();
        this.setItemDataMaxLengthTypesExpected();
        rows.clear();
        logger.debug("Begin to execute GetItemDataMaxLengths");
        rows = select(this.getItemDataMaxLengths(cvIds));
        it = rows.iterator();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            maxLengths.put((Integer) row.get("item_id"), (Integer) row.get("max_length"));
        }*/

        this.setItemGroupAndItemMetaWithUnitTypesExpected();
        rows.clear();
        logger.debug("Begin to execute GetItemGroupAndItemMetaWithUnitSql");
        logger.debug("getItemGroupandItemMetaWithUnitsql= " + this.getItemGroupAndItemMetaWithUnitSql(cvIds));
        rows = select(this.getItemGroupAndItemMetaWithUnitSql(cvIds));
        it = rows.iterator();
        ArrayList<ItemGroupDefBean> itemGroupDefs = (ArrayList<ItemGroupDefBean>) metadata.getItemGroupDefs();
        ArrayList<ItemDefBean> itemDefs = (ArrayList<ItemDefBean>) metadata.getItemDefs();
        ArrayList<CodeListBean> codeLists = (ArrayList<CodeListBean>) metadata.getCodeLists();
        ArrayList<MultiSelectListBean> multiSelectLists = (ArrayList<MultiSelectListBean>) metadata.getMultiSelectLists();
        ArrayList<ElementRefBean> itemGroupRefs = new ArrayList<ElementRefBean>();
        Set<String> igset = new HashSet<String>();
        HashMap<String, Integer> igdPos = new HashMap<String, Integer>();
        Set<String> itset = new HashSet<String>();
        Set<Integer> itdset = new HashSet<Integer>();
        Set<Integer> clset = new HashSet<Integer>();
        Set<Integer> mslset = new HashSet<Integer>();
        ItemGroupDefBean igdef = new ItemGroupDefBean();
        HashMap<String, String> igMandatories = new HashMap<String, String>();
        boolean isLatest = false;
        int cvprev = -1;
        String igrprev = "";
        String igdprev = "";
        String itMandatory = "No";
        int itOrder = 0;
        Integer igId = -1;
        String sectionIds = ",";
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            Integer cId = (Integer) row.get("crf_id");
            Integer cvId = (Integer) row.get("crf_version_id");
            igId = (Integer) row.get("item_group_id");
            Integer itId = (Integer) row.get("item_id");
            Integer rsId = (Integer) row.get("response_set_id");
            String cvOID = (String) row.get("crf_version_oid");
            String igOID = (String) row.get("item_group_oid");
            String itOID = (String) row.get("item_oid");
            String igName = (String) row.get("item_group_name");
            String itName = (String) row.get("item_name");
            Integer dataTypeId = (Integer) row.get("item_data_type_id");
            Integer secId = (Integer) row.get("section_id");
            String header = (String) row.get("item_header");
            String left = (String) row.get("left_item_text");
            String right = (String) row.get("right_item_text");
            Boolean itRequired = (Boolean) row.get("item_required");
            String regexp = (String) row.get("regexp");
            String regexpErr = (String) row.get("regexp_error_msg");
            String widthDecimal = (String) row.get("width_decimal");
            Integer rsTypeId = (Integer) row.get("response_type_id");
            String rsText = (String) row.get("options_text");
            String rsValue = (String) row.get("options_values");
            String rsLabel = (String) row.get("response_label");
            String igHeader = (String) row.get("item_group_header");
            Boolean isRepeating = (Boolean) row.get("repeating_group");
            String itDesc = (String) row.get("item_description");
            String itBriefDesc = (String) row.get("item_brief_description");
            String itQuesNum = (String) row.get("question_number_label");
            String muOid = (String) row.get("mu_oid");
            if (cvprev != cvId) {
                // at this point, itemGroupRefs is for old cvId
                if (itemGroupRefs != null && itemGroupRefs.size() > 0) {
                    String temp = igMandatories.containsKey(igdprev) ? igMandatories.get(igdprev) : itMandatory;
                    itemGroupRefs.get(itemGroupRefs.size() - 1).setMandatory(temp);
                }
                itMandatory = "No";
                // now update to new cvId
                cvprev = cvId;
                FormDefBean formDef = new FormDefBean();
                if (cvIdPoses.containsKey(cvId)) {
                    int p = cvIdPoses.get(cvId);
                    formDef = metadata.getFormDefs().get(p);
                } else {
                    logger.debug("crf_version_id=" + cvId + " couldn't find from studyEventAndFormMetaSql");
                }
                itemGroupRefs = (ArrayList<ElementRefBean>) formDef.getItemGroupRefs();
            }

            // mandatory is based on the last crf-version
            String igDefKey = igId + "";

            if (!igdprev.equals(igDefKey)) {
                if (igdPos.containsKey(igDefKey)) {
                    igdef = itemGroupDefs.get(igdPos.get(igDefKey));
                    isLatest = false;
                    itOrder = igdef.getItemRefs().size();
                } else {
                    igdef = new ItemGroupDefBean();
                    itOrder = 0;
                    igMandatories.put(igdprev, itMandatory);
                    isLatest = true;
                    igdef.setOid(igOID);
                    igdef.setName("ungrouped".equalsIgnoreCase(igName) ? igOID : igName);
                    // igdef.setName(igName);

                    igdef.setRepeating(isRepeating ? "Yes" : "No");

                    igdef.setComment(igHeader);
                    igdef.setPreSASDatasetName(igName.toUpperCase());
                    itemGroupDefs.add(igdef);
                    igdPos.put(igDefKey, itemGroupDefs.size() - 1);
                }
                igdprev = igDefKey;
            }

            String igRefKey = igId + "-" + cvId;
            if (igrprev.equals(igRefKey)) {
                itMandatory = isLatest && itRequired && "No".equals(itMandatory) ? "Yes" : itMandatory;
            } else {
                if (!igset.contains(igRefKey)) {
                    igset.add(igRefKey);
                    ElementRefBean igref = new ElementRefBean();
                    igref.setElementDefOID(igOID);
                    int size = itemGroupRefs.size();
                    if (size > 0) {
                        String prev = igrprev.split("-")[0].trim();
                        String temp = igMandatories.containsKey(prev) ? igMandatories.get(prev) : itMandatory;
                        itemGroupRefs.get(size - 1).setMandatory(temp);
                    }
                    itemGroupRefs.add(igref);
                    itMandatory = "No";
                }
                igrprev = igRefKey;
            }

            String mandatory = itRequired ? "Yes" : "No";
            if (!itset.contains(igDefKey + "-" + itId)) {
                ++itOrder;
                itset.add(igDefKey + "-" + itId);
                ElementRefBean itemRef = new ElementRefBean();
                itemRef.setElementDefOID(itOID);
                if (itemRef.getMandatory() == null || itemRef.getMandatory().length() <= 0) {
                    itemRef.setMandatory(mandatory);
                }
                itemRef.setOrderNumber(itOrder);
                igdef.getItemRefs().add(itemRef);
            }

            boolean hasCode = MetadataUnit.needCodeList(rsTypeId, dataTypeId);
            LinkedHashMap<String, String> codes = new LinkedHashMap<String, String>();
            if (hasCode) {
                /*
                 * //null value will not be added to codelist if (nullMap.containsKey(cvId)) { codes =
                 * MetadataUnit.parseCode(rsText, rsValue,
                 * nullMap.get(cvId)); } else { codes = MetadataUnit.parseCode(rsText, rsValue); }
                 */
                codes = MetadataUnit.parseCode(rsText, rsValue);
                // no action has been taken if rsvalue/rstext go wrong,
                // since they have been validated when uploading crf.
            }

            boolean hasMultiSelect = MetadataUnit.needMultiSelectList(rsTypeId);
            LinkedHashMap<String, String> multi = new LinkedHashMap<String, String>();
            if (hasMultiSelect) {
                multi = MetadataUnit.parseCode(rsText, rsValue);
                // no action has been taken if rsvalue/rstext go wrong,
                // since they have been validated when uploading crf.
            }

            String datatype = MetadataUnit.getOdmItemDataType(rsTypeId, dataTypeId, odmVersion);
            if (sectionIds.contains("," + secId + ",")) {
            } else {
                sectionIds += secId + ",";
            }

            if (!itdset.contains(itId)) {
                itdset.add(itId);
                ItemDefBean idef = new ItemDefBean();
                idef.setOid(itOID);
                idef.setName(itName);
                idef.setComment(itDesc);
                idef.setBriefDescription(itBriefDesc);
                if (muOid != null && muOid.length() > 0) {
                    ElementRefBean measurementUnitRef = new ElementRefBean();
                    measurementUnitRef.setElementDefOID(muOid);
                    idef.setMeasurementUnitRef(measurementUnitRef);
                }
                idef.setPreSASFieldName(itName);
                idef.setCodeListOID(hasCode ? "CL_" + rsId : "");
                if (hasMultiSelect) {
                    ElementRefBean multiSelectListRef = new ElementRefBean();
                    multiSelectListRef.setElementDefOID("MSL_" + rsId);
                    idef.setMultiSelectListRef(multiSelectListRef);
                }
                // if(nullMap.containsKey(cvId)) {
                // }
                idef.getQuestion().setQuestionNumber(itQuesNum);
                idef.getQuestion().getQuestion().setText(MetadataUnit.getItemQuestionText(header, left, right));
                if (regexp != null && regexp.startsWith("func:")) {
                    idef.setRangeCheck(MetadataUnit.getItemRangeCheck(regexp.substring(5).trim(), metadata.getSoftHard(), regexpErr, muOid));
                }
                idef.setDataType(datatype);
                int len = 0;
                int sig = 0;
                if (widthDecimal != null && widthDecimal.length() > 0) {
                    // now there is no validation for width_decimal here.
                    len = parseWidth(widthDecimal);
                    sig = parseDecimal(widthDecimal);
                }
                if (rsTypeId == 3 || rsTypeId == 7) {// 3:checkbox;
                    // //7:multi-select
                    // len = maxLengths.containsKey(itId) ? maxLengths.get(itId) : 0;
                    // len = Math.max(len, Math.max(rsText.length(), rsValue.length()));
                    Iterator<String> iter = multi.keySet().iterator();
                    String temp = "";
                    while (iter.hasNext()) {
                        temp += iter.next();
                        temp += ",";

                    }
                    idef.setLength(temp.length());
                } else if ("text".equalsIgnoreCase(datatype)) {
                    if (len > 0) {
                        idef.setLength(len);
                    } else {
                        idef.setLength(MAX_STRING_LENGTH);
                    }
                } else if ("integer".equalsIgnoreCase(datatype)) {
                    if (len > 0) {
                        idef.setLength(len);
                    } else {
                        idef.setLength(hasCode ? MetadataUnit.getDataTypeLength(codes.keySet()) : 10);
                    }
                } else if ("float".equalsIgnoreCase(datatype)) {
                    if (len > 0) {
                        idef.setLength(len);
                    } else {
                        // idef.setLength(hasCode ?
                        // MetadataUnit.getDataTypeLength(codes.keySet()) : 32);
                        idef.setLength(hasCode ? MetadataUnit.getDataTypeLength(codes.keySet()) : 25);
                    }
                } else {
                    idef.setLength(0);
                }
                idef.setSignificantDigits(sig > 0 ? sig : MetadataUnit.getSignificantDigits(datatype, codes.keySet(), hasCode));
                itemDefs.add(idef);
            }

            if (hasCode && !clset.contains(rsId)) {
                clset.add(rsId);
                CodeListBean cl = new CodeListBean();
                cl.setOid("CL_" + rsId);
                cl.setName(rsLabel);
                cl.setPreSASFormatName(rsLabel);
                cl.setDataType(datatype);
                Iterator<String> iter = codes.keySet().iterator();
                while (iter.hasNext()) {
                    String de = iter.next();
                    CodeListItemBean cli = new CodeListItemBean();
                    cli.setCodedValue(de);
                    TranslatedTextBean tt = cli.getDecode();
                    // cli.getDecode().setText(codes.get(de));
                    tt.setText(codes.get(de));
                    tt.setXmlLang(CoreResources.getField("translated_text_language"));
                    cli.setDecode(tt);
                    cl.getCodeListItems().add(cli);
                }
                codeLists.add(cl);
            }

            if (odmVersion.startsWith("oc") && hasMultiSelect && !mslset.contains(rsId)) {
                mslset.add(rsId);
                MultiSelectListBean msl = new MultiSelectListBean();
                msl.setOid("MSL_" + rsId);
                msl.setName(rsLabel);
                msl.setDataType(datatype);
                msl.setActualDataType(datatype);
                Iterator<String> iter = multi.keySet().iterator();
                while (iter.hasNext()) {
                    String de = iter.next();
                    MultiSelectListItemBean msli = new MultiSelectListItemBean();
                    msli.setCodedOptionValue(de);
                    TranslatedTextBean tt = new TranslatedTextBean();
                    String t = multi.get(de);
                    tt.setText(t);
                    tt.setXmlLang(CoreResources.getField("translated_text_language"));
                    msli.setDecode(tt);
                    msl.getMultiSelectListItems().add(msli);
                }
                multiSelectLists.add(msl);
            }
        }
        if (itemGroupRefs != null && itemGroupRefs.size() > 0) {
            String last = igMandatories.containsKey(igId + "") ? igMandatories.get(igId + "") : itMandatory;
            itemGroupRefs.get(itemGroupRefs.size() - 1).setMandatory(last);
        }
        sectionIds = sectionIds.length() > 1 ? sectionIds.substring(1, sectionIds.length() - 1) : "";
        metadata.setSectionIds(sectionIds);
    }

    public void getODMMetadata(int parentStudyId, int crfVersionOID, MetaDataVersionBean metadata) {

    }

    /**
     * Metadata for ODM_1.2 OpenClinica extension and partial ODM_1.3 OpenClinica extension
     * @param parentStudyId
     * @param studyId
     * @param metadata
     * @param odmVersion
     */
    public void getOCMetadata(int parentStudyId, int studyId, MetaDataVersionBean metadata, String odmVersion) {
        this.getODMMetadata(parentStudyId, studyId, metadata, odmVersion);

        String cvIds = metadata.getCvIds();
        if (odmVersion.startsWith("oc")) {
            // add CRFVersions that itemDef belong to
            HashMap<String, String> itDefCVs = new HashMap<String, String>();
            this.setItemCVOIDsTypesExpected();
            ArrayList al = this.select(this.getItemCVOIDsSqlUpdated(cvIds));
            Iterator iter = al.iterator();
            while (iter.hasNext()) {
                HashMap row = (HashMap) iter.next();
                Integer cId = (Integer) row.get("crf_id");
                Integer cvId = (Integer) row.get("crf_version_id");
                Integer itId = (Integer) row.get("item_id");
                String cvOID = (String) row.get("cv_oid");
                String itOID = (String) row.get("item_oid");
                String crfOID = (String) row.get("crf_oid");

                if (itDefCVs.containsKey(itOID)) {
                    String cvs = itDefCVs.get(itOID);
                    if (!cvs.contains(crfOID + ",")) {
                        cvs += crfOID + ",";
                    }
                    itDefCVs.put(itOID, cvs);
                } else {
                    itDefCVs.put(itOID, crfOID + ",");
                }
            }
            for (ItemDefBean itdef : metadata.getItemDefs()) {
                String key = itdef.getOid();
                if (itDefCVs.containsKey(key)) {
                    String cvs = itDefCVs.get(key);
                    itdef.setFormOIDs(cvs.substring(0, cvs.length() - 1));
                }
            }

            // add StudyGroupClassList
            this.setStudyGroupClassTypesExpected();
            logger.debug("Begin to execute GetStudyGroupClassSql");
            logger.debug("getStudyGroupClassSql=" + getStudyGroupClassSql(parentStudyId));
            ArrayList rows = select(this.getStudyGroupClassSql(parentStudyId));
            Iterator it = rows.iterator();
            ArrayList<StudyGroupClassListBean> sgcLists = (ArrayList<StudyGroupClassListBean>) metadata.getStudyGroupClassLists();
            String sgcprev = "";
            while (it.hasNext()) {
                HashMap row = (HashMap) it.next();
                Integer sgcid = (Integer) row.get("study_group_class_id");
                String sgcname = (String) row.get("sgc_name");
                String sgctype = (String) row.get("sgc_type");
                Integer statusid = (Integer) row.get("status_id");
                String subassign = (String) row.get("subject_assignment");
                String sgname = (String) row.get("sg_name");
                String des = (String) row.get("description");

                if (sgcprev.equals(sgcid + "")) {
                    StudyGroupItemBean sg = new StudyGroupItemBean();
                    sg.setName(sgname);
                    sg.setDescription(des);
                    StudyGroupClassListBean sgc = sgcLists.get(sgcLists.size() - 1);
                    sgc.getStudyGroupItems().add(sg);
                } else {
                    sgcprev = sgcid + "";
                    StudyGroupClassListBean sgc = new StudyGroupClassListBean();
                    sgc.setID("SGC_" + sgcid);
                    sgc.setName(sgcname);
                    sgc.setType(sgctype);
                    sgc.setStatus(Status.get(statusid).getName());
                    sgc.setSubjectAssignment(subassign);
                    StudyGroupItemBean sg = new StudyGroupItemBean();
                    sg.setName(sgname);
                    sg.setDescription(des);
                    sgc.getStudyGroupItems().add(sg);
                    sgcLists.add(sgc);
                }
            }
        }
        // return nullClSet;
    }

    public void getOCMetadataForGlobals(int crfId, MetaDataVersionBean metadata, String odmVersion) {

        String cvIds = crfId + "";
        if (odmVersion.startsWith("oc")) {
            // add CRFVersions that itemDef belong to
            HashMap<String, String> itDefCVs = new HashMap<String, String>();
            this.setItemCVOIDsTypesExpected();
            ArrayList al = this.select(this.getItemCVOIDsSqlUpdated(cvIds));
            Iterator iter = al.iterator();
            while (iter.hasNext()) {
                HashMap row = (HashMap) iter.next();
                Integer cId = (Integer) row.get("crf_id");
                Integer cvId = (Integer) row.get("crf_version_id");
                Integer itId = (Integer) row.get("item_id");
                String cvOID = (String) row.get("cv_oid");
                String itOID = (String) row.get("item_oid");
                String crfOID = (String) row.get("crf_oid");

                if (itDefCVs.containsKey(itOID)) {
                    String cvs = itDefCVs.get(itOID);
                    if (!cvs.contains(crfOID + ",")) {
                        cvs += crfOID + ",";
                    }
                    itDefCVs.put(itOID, cvs);
                } else {
                    itDefCVs.put(itOID, crfOID + ",");
                }
            }
            for (ItemDefBean itdef : metadata.getItemDefs()) {
                String key = itdef.getOid();
                if (itDefCVs.containsKey(key)) {
                    String cvs = itDefCVs.get(key);
                    itdef.setFormOIDs(cvs.substring(0, cvs.length() - 1));
                }
            }

            // add StudyGroupClassList
            /*
             * this.setStudyGroupClassTypesExpected();
             * logger.debug("Begin to execute GetStudyGroupClassSql");
             * logger.debug("getStudyGroupClassSql=" + getStudyGroupClassSql(parentStudyId));
             * ArrayList rows = select(this.getStudyGroupClassSql(parentStudyId));
             * Iterator it = rows.iterator();
             * ArrayList<StudyGroupClassListBean> sgcLists = (ArrayList<StudyGroupClassListBean>)
             * metadata.getStudyGroupClassLists();
             * String sgcprev = "";
             * while (it.hasNext()) {
             * HashMap row = (HashMap) it.next();
             * Integer sgcid = (Integer) row.get("study_group_class_id");
             * String sgcname = (String) row.get("sgc_name");
             * String sgctype = (String) row.get("sgc_type");
             * Integer statusid = (Integer) row.get("status_id");
             * String subassign = (String) row.get("subject_assignment");
             * String sgname = (String) row.get("sg_name");
             * String des = (String) row.get("description");
             *
             * if (sgcprev.equals(sgcid + "")) {
             * StudyGroupItemBean sg = new StudyGroupItemBean();
             * sg.setName(sgname);
             * sg.setDescription(des);
             * StudyGroupClassListBean sgc = sgcLists.get(sgcLists.size() - 1);
             * sgc.getStudyGroupItems().add(sg);
             * } else {
             * sgcprev = sgcid + "";
             * StudyGroupClassListBean sgc = new StudyGroupClassListBean();
             * sgc.setID("SGC_" + sgcid);
             * sgc.setName(sgcname);
             * sgc.setType(sgctype);
             * sgc.setStatus(Status.get(statusid).getName());
             * sgc.setSubjectAssignment(subassign);
             * StudyGroupItemBean sg = new StudyGroupItemBean();
             * sg.setName(sgname);
             * sg.setDescription(des);
             * sgc.getStudyGroupItems().add(sg);
             * sgcLists.add(sgc);
             * }
             * }
             */
        }
        // return nullClSet;
    }

    public void getStudyEventAndFormMetaOC1_3(Study parentStudy, Study study, MetaDataVersionBean metadata, String odmVersion, boolean isIncludedSite) {
        ArrayList<StudyEventDefBean> seds = (ArrayList<StudyEventDefBean>) metadata.getStudyEventDefs();
        ArrayList<FormDefBean> forms = (ArrayList<FormDefBean>) metadata.getFormDefs();

        for (StudyEventDefBean sed : seds) {
            List<ElementRefBean> formRefs = sed.getFormRefs();
            for (ElementRefBean formRef : formRefs) {
                ConfigurationParameters conf = new ConfigurationParameters();
                EventDefinitionCRFBean edc = getEventDefCRF(sed, formRef, study);
                CRFBean cBean = (CRFBean) crfdao.findByPK(edc.getCrfId());
                String crfPath = sed.getOid() + "." + cBean.getOid();
                conf = populateConfigurationParameters(edc, conf, crfPath);
                formRef.setConfigurationParameters(conf);
                int defaultVersionId = edc.getDefaultVersionId();
                formRef.setFormLayoutRefs(getFormLayoutRefs(formRef, defaultVersionId));
            }

        }

        HashMap<String, EventDefinitionDetailsBean> sedDetails = new HashMap<String, EventDefinitionDetailsBean>();
        HashMap<String, FormDetailsBean> formDetails = new HashMap<String, FormDetailsBean>();

        this.setStudyEventAndFormMetaOC1_3TypesExpected();
        logger.debug("Begin to execute GetStudyEventAndFormMetaOC1_3Sql");
        logger.debug("getStudyEventAndFormMetaOC1_3SQl= {} ", this.getStudyEventAndFormMetaOC1_3Sql(parentStudy.getStudyId(), study.getStudyId(), isIncludedSite, showArchivedSql(showArchived)));

        ArrayList rows = this.select(this.getStudyEventAndFormMetaOC1_3Sql(parentStudy.getStudyId(), study.getStudyId(), isIncludedSite, showArchivedSql(showArchived)));
        Iterator iter = rows.iterator();
        String sedOIDs = "";
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            Integer cvId = (Integer) row.get("crf_version_id");
            String sedOID = (String) row.get("definition_oid");
            String cvOID = (String) row.get("cv_oid");
            String sedDesc = (String) row.get("description");
            String category = (String) row.get("category");
            String cvDesc = (String) row.get("version_description");
            String notes = (String) row.get("revision_notes");
            String cOID = (String) row.get("crf_oid");
            String crfDescription = (String) row.get("crf_description");
            String nullValue = (String) row.get("null_values");
            Integer dvId = (Integer) row.get("default_version_id");
            Boolean pwdRequired = (Boolean) row.get("electronic_signature");
            Boolean doubleEntry = (Boolean) row.get("double_entry");
            Boolean hideCrf = (Boolean) row.get("hide_crf");
            Boolean participantForm = (Boolean) row.get("participant_form");
            Boolean allowAnonymousSubmission = (Boolean) row.get("allow_anonymous_submission");
            String submissionUrl = (String) row.get("submission_url");
            Integer sdvId = (Integer) row.get("source_data_verification_code");
            Boolean offline = (Boolean) row.get("active");

            if (sedDetails.containsKey(sedOID)) {
            } else {
                EventDefinitionDetailsBean sedDetail = new EventDefinitionDetailsBean();
                sedDetail.setOid(sedOID);
                sedDetail.setCategory(category);
                sedDetail.setDescription(sedDesc);
                sedDetails.put(sedOID, sedDetail);
            }

            if (formDetails.containsKey(cOID)) {

                FormDetailsBean formDetail = formDetails.get(cOID);
                PresentInEventDefinitionBean p = new PresentInEventDefinitionBean();
                p.setStudyEventOid(sedOID);
                p.setDoubleDataEntry(doubleEntry == false ? "No" : "Yes");
                p.setHideCrf(hideCrf == false ? "No" : "Yes");
                p.setParticipantForm(participantForm == false ? "No" : "Yes");
                p.setIsDefaultVersion(cvId.equals(dvId) ? "Yes" : "No");
                p.setNullValues(nullValue);
                p.setPasswordRequired(pwdRequired == false ? "No" : "Yes");
                p.setAllowAnonymousSubmission(allowAnonymousSubmission == false ? "No" : "Yes");
                p.setOffline((offline == false) ? "No" : "Yes");
                p.setSubmissionUrl(submissionUrl);
                p.setSourceDataVerification(SourceDataVerification.getByCode(sdvId > 0 ? sdvId : 3).getDescription());
                formDetail.getPresentInEventDefinitions().add(p);
            } else {
                FormDetailsBean formDetail = new FormDetailsBean();
                formDetail.setOid(cOID);
                formDetail.setRevisionNotes(notes);
                formDetail.setParentFormOid(cOID);
                formDetail.setVersionDescription(cvDesc);
                formDetail.setDescription(crfDescription);
                // ArrayList sectionBeansRows = this.select(this.getSectionDetails(cvOID),cvId);
                formDetail = setSectionBean(formDetail, cvId);
                PresentInEventDefinitionBean p = new PresentInEventDefinitionBean();
                p.setStudyEventOid(sedOID);
                p.setDoubleDataEntry(doubleEntry == false ? "No" : "Yes");
                p.setHideCrf(hideCrf == false ? "No" : "Yes");
                p.setParticipantForm(participantForm == false ? "No" : "Yes");
                p.setIsDefaultVersion(cvId.equals(dvId) ? "Yes" : "No");
                p.setNullValues(nullValue);
                p.setPasswordRequired(pwdRequired == false ? "No" : "Yes");
                p.setAllowAnonymousSubmission(allowAnonymousSubmission == false ? "No" : "Yes");
                p.setOffline((offline == false) ? "No" : "Yes");
                p.setSubmissionUrl(submissionUrl);

                p.setSourceDataVerification(SourceDataVerification.getByCode(sdvId > 0 ? sdvId : 3).getDescription());
                formDetail.getPresentInEventDefinitions().add(p);

                formDetails.put(cOID, formDetail);
            }
        }

        for (StudyEventDefBean sedef : seds) {
            sedef.setEventDefinitionDetais(sedDetails.get(sedef.getOid()));
        }
        for (FormDefBean formdef : forms) {
            List<ElementRefBean> formLayoutRefs = getFormLayoutRefs(formdef);
            formdef.setFormLayoutRefs(formLayoutRefs);
            formdef.setFormDetails(formDetails.get(formdef.getOid()));
        }
    }

    private FormDetailsBean setSectionBean(FormDetailsBean formDetail, Integer crfVId) {

        HashMap variables = new HashMap();
        variables.put(new Integer(1), new Integer(crfVId));
        ArrayList<SectionDetails> sectionBeans = new ArrayList<SectionDetails>();

        SectionDAO secdao = new SectionDAO(this.ds);
        ArrayList sections = secdao.findAllByCRFVersionId(crfVId);
        Iterator iter = sections.iterator();
        while (iter.hasNext()) {
            SectionDetails sectionDetails = new SectionDetails();
            SectionBean sectionBean = (SectionBean) iter.next();
            sectionDetails.setSectionId(sectionBean.getId());
            sectionDetails.setSectionLabel(sectionBean.getLabel());
            sectionDetails.setSectionTitle(sectionBean.getTitle());
            sectionDetails.setSectionSubtitle(sectionBean.getSubtitle());
            sectionDetails.setSectionInstructions(sectionBean.getInstructions());
            sectionDetails.setSectionPageNumber(sectionBean.getPageNumberLabel());

            sectionBeans.add(sectionDetails);
        }
        formDetail.setSectionDetails(sectionBeans);
        return formDetail;
    }

    public void getMetadataOC1_3(Study parentStudy, Study study, MetaDataVersionBean metadata, String odmVersion) {
        this.getOCMetadata(parentStudy.getStudyId(), study.getStudyId(), metadata, odmVersion);

        // StudyBean study = metadata.getStudy();
        // if(study.getId()>0) {
        // } else {
        // StudyDAO sdao = new StudyDAO(this.ds);
        // study = (StudyBean)sdao.findByPK(studyId);
        // }
        // StudyConfigService studyConfig = new StudyConfigService(this.ds);
        // study = studyConfig.setParametersForStudy(study);

        this.getStudyEventAndFormMetaOC1_3(parentStudy, study, metadata, odmVersion, false);

        String cvIds = metadata.getCvIds();
        ArrayList<ItemGroupDefBean> igs = (ArrayList<ItemGroupDefBean>) metadata.getItemGroupDefs();
        HashMap<String, Integer> igPoses = getItemGroupOIDPos(metadata);
        ArrayList<ItemDefBean> its = (ArrayList<ItemDefBean>) metadata.getItemDefs();
        HashMap<String, Integer> itPoses = getItemOIDPos(metadata);
        HashMap<String, Integer> inPoses = new HashMap<String, Integer>();
        ItemGroupDefBean ig = new ItemGroupDefBean();
        ItemDefBean it = new ItemDefBean();
        String prevCvIg = "";
        HashMap<Integer, String> sectionLabels = this.getSectionLabels(metadata.getSectionIds());
        HashMap<Integer, String> parentItemOIDs = this.getParentItemOIDs(cvIds);
        this.setItemGroupAndItemMetaOC1_3TypesExpected();
        logger.debug("Begin to execute GetItemGroupAndItemMetaWithUnitSql");
        logger.debug("getItemGroupandItemMetaWithUnitsql= " + this.getItemGroupAndItemMetaOC1_3Sql(cvIds));
        ArrayList rows = select(this.getItemGroupAndItemMetaOC1_3Sql(cvIds));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            // Integer cId = (Integer) row.get("crf_id");
            Integer cvId = (Integer) row.get("crf_version_id");
            Integer igId = (Integer) row.get("item_group_id");
            // Integer itId = (Integer) row.get("item_id");
            // Integer rsId = (Integer) row.get("response_set_id");
            String cvOID = (String) row.get("crf_version_oid");
            String igOID = (String) row.get("item_group_oid");
            String itOID = (String) row.get("item_oid");

            Integer igRepeatNum = (Integer) row.get("repeat_number");
            Integer igRepeatMax = (Integer) row.get("repeat_max");
            Boolean showGroup = (Boolean) row.get("show_group");
            String itemGroupHeader = (String) row.get("item_group_header");

            String itHeader = (String) row.get("item_header");
            String left = (String) row.get("left_item_text");
            String right = (String) row.get("right_item_text");
            String itSubheader = (String) row.get("subheader");
            Integer itSecId = (Integer) row.get("section_id");
            Integer itPId = (Integer) row.get("parent_id");
            Integer itColNum = (Integer) row.get("column_number");
            String itpgNum = (String) row.get("page_number_label");
            String layout = (String) row.get("response_layout");
            Integer rsTypeId = (Integer) row.get("response_type_id");
            String dfValue = (String) row.get("default_value");
            Boolean phi = (Boolean) row.get("phi_status");
            Boolean showItem = (Boolean) row.get("show_item");
            Integer orderInForm = (Integer) row.get("item_order");
            if ((cvId + "-" + igId).equals(prevCvIg)) {
            } else {
                prevCvIg = cvId + "-" + igId;
                ig = igs.get(igPoses.get(igOID));
                ItemGroupDetailsBean igDetail = ig.getItemGroupDetails();
                igDetail.setOid(igOID);
                PresentInFormBean inForm = new PresentInFormBean();
                inForm.setFormOid(cvOID);
                ItemGroupRepeatBean igr = new ItemGroupRepeatBean();
                igr.setRepeatMax(igRepeatMax);
                igr.setRepeatNumber(igRepeatNum);
                inForm.setItemGroupRepeatBean(igr);
                inForm.setShowGroup(showGroup == true ? "Yes" : "No");
                inForm.setItemGroupHeader(itemGroupHeader);
                igDetail.getPresentInForms().add(inForm);
            }

            it = its.get(itPoses.get(itOID));
            ItemDetailsBean itDetail = it.getItemDetails();
            itDetail.setOid(itOID);
            ItemPresentInFormBean itInForm = new ItemPresentInFormBean();
            itInForm.setFormOid(cvOID);
            itInForm.setColumnNumber(itColNum);
            itInForm.setDefaultValue(dfValue);
            itInForm.setItemHeader(itHeader);
            itInForm.setLeftItemText(left);
            itInForm.setRightItemText(right);
            itInForm.setItemSubHeader(itSubheader);
            itInForm.setPageNumber(itpgNum);
            itInForm.setParentItemOid(parentItemOIDs.get(itPId));
            itInForm.setSectionLabel(sectionLabels.get(itSecId));
            itInForm.setPhi(phi == false ? "No" : "Yes");
            itInForm.setOrderInForm(orderInForm);
            itInForm.setShowItem(showItem == true ? "Yes" : "No");
            ItemResponseBean itemResponse = new ItemResponseBean();
            itemResponse.setResponseLayout(layout);
            itemResponse.setResponseType(ResponseType.get(rsTypeId).getName());
            itInForm.setItemResponse(itemResponse);
            itDetail.getItemPresentInForm().add(itInForm);
            inPoses.put(itOID + "-" + cvOID, itDetail.getItemPresentInForm().size() - 1);
        }

        this.getSCDs(cvIds, its, itPoses, inPoses);
    }

    protected void getSCDs(String crfVersionIds, ArrayList<ItemDefBean> its, HashMap<String, Integer> itPoses, HashMap<String, Integer> inFormPoses) {
        logger.debug("Begin to execute getSCDsSql");
        this.setSCDsTypesExpected();
        ArrayList rows = this.select(this.getSCDsSql(crfVersionIds));
        if (rows == null || rows.size() < 1) {
            logger.info("OdmExtracDAO.getSCDsSql returns no rows.");
        } else {
            Iterator iter = rows.iterator();
            while (iter.hasNext()) {
                HashMap row = (HashMap) iter.next();
                String cvOID = (String) row.get("crf_version_oid");
                String itOID = (String) row.get("item_oid");
                String controlItemName = (String) row.get("control_item_name");
                String option = (String) row.get("option_value");
                String message = (String) row.get("message");

                if (controlItemName != null && controlItemName.length() > 0 && option != null && option.length() > 0 && message != null
                        && message.length() > 0) {
                    SimpleConditionalDisplayBean scd = new SimpleConditionalDisplayBean();
                    scd.setControlItemName(controlItemName);
                    scd.setOptionValue(option);
                    scd.setMessage(message);
                    if (itPoses.containsKey(itOID) && inFormPoses.containsKey(itOID + "-" + cvOID)) {
                        its.get(itPoses.get(itOID)).getItemDetails().getItemPresentInForm().get(inFormPoses.get(itOID + "-" + cvOID))
                                .setSimpleConditionalDisplay(scd);
                    } else {
                        logger.info("There is no <ItemDef> with item_oid=" + itOID + " or has <ItemPresentInForm> with FormOID=" + cvOID + ".");
                    }
                } else {
                    logger.info("No Simple Conditional Display added for <ItemDef> with crf_version_oid=" + cvOID + " and item_oid=" + itOID);
                }
            }
        }
    }

    private void fetchItemGroupMetaData(MetaDataVersionBean metadata, String cvIds, String odmVersion)

    {
        ArrayList rows = select(this.getItemDataMaxLengths(cvIds));
        Iterator it = rows.iterator();
        HashMap maxLengths = null;
        // while (it.hasNext()) {
        // HashMap row = (HashMap) it.next();
        // maxLengths.put((Integer) row.get("item_id"), (Integer) row.get("max_length"));
        // }

        this.setItemGroupAndItemMetaWithUnitTypesExpected();
        rows.clear();
        logger.debug("Begin to execute GetItemGroupAndItemMetaWithUnitSql");
        logger.debug("getItemGroupandItemMetaWithUnitsql= " + this.getItemGroupAndItemMetaWithUnitSql(cvIds));
        rows = select(this.getItemGroupAndItemMetaWithUnitSql(cvIds));
        it = rows.iterator();
        ArrayList<ItemGroupDefBean> itemGroupDefs = (ArrayList<ItemGroupDefBean>) metadata.getItemGroupDefs();
        ArrayList<ItemDefBean> itemDefs = (ArrayList<ItemDefBean>) metadata.getItemDefs();
        ArrayList<CodeListBean> codeLists = (ArrayList<CodeListBean>) metadata.getCodeLists();
        ArrayList<MultiSelectListBean> multiSelectLists = (ArrayList<MultiSelectListBean>) metadata.getMultiSelectLists();
        ArrayList<ElementRefBean> itemGroupRefs = new ArrayList<ElementRefBean>();
        Set<String> igset = new HashSet<String>();
        HashMap<String, Integer> igdPos = new HashMap<String, Integer>();
        Set<String> itset = new HashSet<String>();
        Set<Integer> itdset = new HashSet<Integer>();
        Set<Integer> clset = new HashSet<Integer>();
        Set<Integer> mslset = new HashSet<Integer>();
        ItemGroupDefBean igdef = new ItemGroupDefBean();
        HashMap<String, String> igMandatories = new HashMap<String, String>();
        boolean isLatest = false;
        int cvprev = -1;
        String igrprev = "";
        String igdprev = "";
        String itMandatory = "No";
        int itOrder = 0;
        Integer igId = -1;
        String sectionIds = ",";
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            Integer cId = (Integer) row.get("crf_id");
            Integer cvId = (Integer) row.get("crf_version_id");
            igId = (Integer) row.get("item_group_id");
            Integer itId = (Integer) row.get("item_id");
            Integer rsId = (Integer) row.get("response_set_id");
            String cvOID = (String) row.get("crf_version_oid");
            String igOID = (String) row.get("item_group_oid");
            String itOID = (String) row.get("item_oid");
            String igName = (String) row.get("item_group_name");
            String itName = (String) row.get("item_name");
            Integer dataTypeId = (Integer) row.get("item_data_type_id");
            Integer secId = (Integer) row.get("section_id");
            String header = (String) row.get("item_header");
            String left = (String) row.get("left_item_text");
            String right = (String) row.get("right_item_text");
            Boolean itRequired = (Boolean) row.get("item_required");
            String regexp = (String) row.get("regexp");
            String regexpErr = (String) row.get("regexp_error_msg");
            String widthDecimal = (String) row.get("width_decimal");
            Integer rsTypeId = (Integer) row.get("response_type_id");
            String rsText = (String) row.get("options_text");
            String rsValue = (String) row.get("options_values");
            String rsLabel = (String) row.get("response_label");
            String igHeader = (String) row.get("item_group_header");

            Boolean isRepeating = (Boolean) row.get("repeating_group");
            String itDesc = (String) row.get("item_description");
            String itBriefDesc = (String) row.get("item_brief_description");
            String itQuesNum = (String) row.get("question_number_label");
            String muOid = (String) row.get("mu_oid");
            if (cvprev != cvId) {
                // at this point, itemGroupRefs is for old cvId
                if (itemGroupRefs != null && itemGroupRefs.size() > 0) {
                    String temp = igMandatories.containsKey(igdprev) ? igMandatories.get(igdprev) : itMandatory;
                    itemGroupRefs.get(itemGroupRefs.size() - 1).setMandatory(temp);
                }
                itMandatory = "No";
                // now update to new cvId
                cvprev = cvId;
                FormDefBean formDef = new FormDefBean();
                HashMap cvIdPoses = null;
                // if (cvIdPoses.containsKey(cvId)) {
                // int p = (Integer) cvIdPoses.get(cvId);
                // formDef = metadata.getFormDefs().get(p);
                // } else {
                // logger.debug("crf_version_id=" + cvId + " couldn't find from studyEventAndFormMetaSql");
                // }
                itemGroupRefs = (ArrayList<ElementRefBean>) formDef.getItemGroupRefs();
            }

            // mandatory is based on the last crf-version
            String igDefKey = igId + "";

            if (!igdprev.equals(igDefKey)) {
                if (igdPos.containsKey(igDefKey)) {
                    igdef = itemGroupDefs.get(igdPos.get(igDefKey));
                    isLatest = false;
                    itOrder = igdef.getItemRefs().size();
                } else {
                    igdef = new ItemGroupDefBean();
                    itOrder = 0;
                    igMandatories.put(igdprev, itMandatory);
                    isLatest = true;
                    igdef.setOid(igOID);
                    igdef.setName("ungrouped".equalsIgnoreCase(igName) ? igOID : igName);
                    // igdef.setName(igName);
                    igdef.setRepeating(isRepeating ? "Yes" : "No");
                    igdef.setComment(igHeader);
                    igdef.setPreSASDatasetName(igName.toUpperCase());
                    itemGroupDefs.add(igdef);
                    igdPos.put(igDefKey, itemGroupDefs.size() - 1);
                }
                igdprev = igDefKey;
            }

            String igRefKey = igId + "-" + cvId;
            if (igrprev.equals(igRefKey)) {
                itMandatory = isLatest && itRequired && "No".equals(itMandatory) ? "Yes" : itMandatory;
            } else {
                if (!igset.contains(igRefKey)) {
                    igset.add(igRefKey);
                    ElementRefBean igref = new ElementRefBean();
                    igref.setElementDefOID(igOID);
                    int size = itemGroupRefs.size();
                    if (size > 0) {
                        String prev = igrprev.split("-")[0].trim();
                        String temp = igMandatories.containsKey(prev) ? igMandatories.get(prev) : itMandatory;
                        itemGroupRefs.get(size - 1).setMandatory(temp);
                    }
                    itemGroupRefs.add(igref);
                    itMandatory = "No";
                }
                igrprev = igRefKey;
            }

            String mandatory = itRequired ? "Yes" : "No";
            if (!itset.contains(igDefKey + "-" + itId)) {
                ++itOrder;
                itset.add(igDefKey + "-" + itId);
                ElementRefBean itemRef = new ElementRefBean();
                itemRef.setElementDefOID(itOID);
                if (itemRef.getMandatory() == null || itemRef.getMandatory().length() <= 0) {
                    itemRef.setMandatory(mandatory);
                }
                itemRef.setOrderNumber(itOrder);
                igdef.getItemRefs().add(itemRef);
            }

            boolean hasCode = MetadataUnit.needCodeList(rsTypeId, dataTypeId);
            LinkedHashMap<String, String> codes = new LinkedHashMap<String, String>();
            if (hasCode) {
                /*
                 * //null value will not be added to codelist if (nullMap.containsKey(cvId)) { codes =
                 * MetadataUnit.parseCode(rsText, rsValue,
                 * nullMap.get(cvId)); } else { codes = MetadataUnit.parseCode(rsText, rsValue); }
                 */
                codes = MetadataUnit.parseCode(rsText, rsValue);
                // no action has been taken if rsvalue/rstext go wrong,
                // since they have been validated when uploading crf.
            }

            boolean hasMultiSelect = MetadataUnit.needMultiSelectList(rsTypeId);
            LinkedHashMap<String, String> multi = new LinkedHashMap<String, String>();
            if (hasMultiSelect) {
                multi = MetadataUnit.parseCode(rsText, rsValue);
                // no action has been taken if rsvalue/rstext go wrong,
                // since they have been validated when uploading crf.
            }

            String datatype = MetadataUnit.getOdmItemDataType(rsTypeId, dataTypeId, odmVersion);
            if (sectionIds.contains("," + secId + ",")) {
            } else {
                sectionIds += secId + ",";
            }

            if (!itdset.contains(itId)) {
                itdset.add(itId);
                ItemDefBean idef = new ItemDefBean();
                idef.setOid(itOID);
                idef.setName(itName);
                idef.setComment(itDesc);
                idef.setBriefDescription(itBriefDesc);
                if (muOid != null && muOid.length() > 0) {
                    ElementRefBean measurementUnitRef = new ElementRefBean();
                    measurementUnitRef.setElementDefOID(muOid);
                    idef.setMeasurementUnitRef(measurementUnitRef);
                }
                idef.setPreSASFieldName(itName);
                idef.setCodeListOID(hasCode ? "CL_" + rsId : "");
                if (hasMultiSelect) {
                    ElementRefBean multiSelectListRef = new ElementRefBean();
                    multiSelectListRef.setElementDefOID("MSL_" + rsId);
                    idef.setMultiSelectListRef(multiSelectListRef);
                }
                // if(nullMap.containsKey(cvId)) {
                // }
                idef.getQuestion().setQuestionNumber(itQuesNum);
                idef.getQuestion().getQuestion().setText(MetadataUnit.getItemQuestionText(header, left, right));
                if (regexp != null && regexp.startsWith("func:")) {
                    idef.setRangeCheck(MetadataUnit.getItemRangeCheck(regexp.substring(5).trim(), metadata.getSoftHard(), regexpErr, muOid));
                }
                idef.setDataType(datatype);
                int len = 0;
                int sig = 0;
                if (widthDecimal != null && widthDecimal.length() > 0) {
                    // now there is no validation for width_decimal here.
                    len = parseWidth(widthDecimal);
                    sig = parseDecimal(widthDecimal);
                }
                if (rsTypeId == 3 || rsTypeId == 7) {// 3:checkbox;
                    // //7:multi-select
                    // len = maxLengths.containsKey(itId) ? maxLengths.get(itId) : 0;
                    // len = Math.max(len, Math.max(rsText.length(), rsValue.length()));
                    Iterator<String> iter = multi.keySet().iterator();
                    String temp = "";
                    while (iter.hasNext()) {
                        temp += iter.next();
                        temp += ",";

                    }
                    idef.setLength(temp.length());
                } else if ("text".equalsIgnoreCase(datatype)) {
                    if (len > 0) {
                        idef.setLength(len);
                    } else {
                        // idef.setLength((Integer) (hasCode ? MetadataUnit.getDataTypeLength(codes.keySet()) :
                        // maxLengths.containsKey(itId) ? maxLengths.get(itId) : MetaDataCollector.getTextLength()));

                        idef.setLength(0);
                    }
                } else if ("integer".equalsIgnoreCase(datatype)) {
                    if (len > 0) {
                        idef.setLength(len);
                    } else {
                        idef.setLength(hasCode ? MetadataUnit.getDataTypeLength(codes.keySet()) : 10);
                    }
                } else if ("float".equalsIgnoreCase(datatype)) {
                    if (len > 0) {
                        idef.setLength(len);
                    } else {
                        // idef.setLength(hasCode ?
                        // MetadataUnit.getDataTypeLength(codes.keySet()) : 32);
                        idef.setLength(hasCode ? MetadataUnit.getDataTypeLength(codes.keySet()) : 25);
                    }
                } else {
                    idef.setLength(0);
                }
                idef.setSignificantDigits(sig > 0 ? sig : MetadataUnit.getSignificantDigits(datatype, codes.keySet(), hasCode));
                itemDefs.add(idef);
            }

            if (hasCode && !clset.contains(rsId)) {
                clset.add(rsId);
                CodeListBean cl = new CodeListBean();
                cl.setOid("CL_" + rsId);
                cl.setName(rsLabel);
                cl.setPreSASFormatName(rsLabel);
                cl.setDataType(datatype);
                Iterator<String> iter = codes.keySet().iterator();
                while (iter.hasNext()) {
                    String de = iter.next();
                    CodeListItemBean cli = new CodeListItemBean();
                    cli.setCodedValue(de);
                    TranslatedTextBean tt = cli.getDecode();
                    // cli.getDecode().setText(codes.get(de));
                    tt.setText(codes.get(de));
                    tt.setXmlLang(CoreResources.getField("translated_text_language"));
                    cli.setDecode(tt);
                    cl.getCodeListItems().add(cli);
                }
                codeLists.add(cl);
            }

            if (odmVersion.startsWith("oc") && hasMultiSelect && !mslset.contains(rsId)) {
                mslset.add(rsId);
                MultiSelectListBean msl = new MultiSelectListBean();
                msl.setOid("MSL_" + rsId);
                msl.setName(rsLabel);
                msl.setDataType(datatype);
                msl.setActualDataType(datatype);
                Iterator<String> iter = multi.keySet().iterator();
                while (iter.hasNext()) {
                    String de = iter.next();
                    MultiSelectListItemBean msli = new MultiSelectListItemBean();
                    msli.setCodedOptionValue(de);
                    TranslatedTextBean tt = new TranslatedTextBean();
                    String t = multi.get(de);
                    tt.setText(t);
                    tt.setXmlLang(CoreResources.getField("translated_text_language"));
                    msli.setDecode(tt);
                    msl.getMultiSelectListItems().add(msli);
                }
                multiSelectLists.add(msl);
            }
        }
        if (itemGroupRefs != null && itemGroupRefs.size() > 0) {
            String last = igMandatories.containsKey(igId + "") ? igMandatories.get(igId + "") : itMandatory;
            itemGroupRefs.get(itemGroupRefs.size() - 1).setMandatory(last);
        }
        sectionIds = sectionIds.length() > 0 ? sectionIds.substring(1, sectionIds.length() - 1) : "";
        metadata.setSectionIds(sectionIds);
    }

    public int parseWidth(String widthDecimal) {
        String w = "";
        widthDecimal = widthDecimal.trim();
        if (widthDecimal.startsWith("(")) {
        } else if (widthDecimal.contains("(")) {
            w = widthDecimal.split("\\(")[0];
        } else {
            w = widthDecimal;
        }
        if (w.length() > 0) {
            return "w".equalsIgnoreCase(w) ? 0 : Integer.parseInt(w);
        }
        return 0;
    }

    public int parseDecimal(String widthDecimal) {
        String d = "";
        widthDecimal = widthDecimal.trim();
        if (widthDecimal.startsWith("(")) {
            d = widthDecimal.substring(1, widthDecimal.length() - 1);
        } else if (widthDecimal.contains("(")) {
            d = widthDecimal.split("\\(")[1].trim();
            d = d.substring(0, d.length() - 1);

        }
        if (d.length() > 0) {
            return "d".equalsIgnoreCase(d) ? 0 : Integer.parseInt(d);
        }
        return 0;
    }

    public void getAdminData(Study study, DatasetBean dataset, OdmAdminDataBean data, String odmVersion) {
        String dbName = CoreResources.getDBName();
        this.setStudyUsersTypesExpected();
        ArrayList rows = this.select(this.getStudyUsersSql(study.getStudyId() + ""));
        Iterator it = rows.iterator();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            Integer userId = (Integer) row.get("user_id");
            String firstName = (String) row.get("first_name");
            String lastName = (String) row.get("last_name");
            String organization = (String) row.get("institutional_affiliation");

            UserBean user = new UserBean();
            user.setOid("USR_" + userId);
            user.setFirstName(firstName);
            user.setLastName(lastName);
            user.setOrganization(organization);
            data.getUsers().add(user);
        }
        // LocationBean loc = new LocationBean();
        // loc.setOid("LOC_"+study.getOid());
        // loc.setName(study.getName());
        MetaDataVersionRefBean meta = new MetaDataVersionRefBean();
        meta.setElementDefOID(data.getMetaDataVersionOID());
        meta.setStudyOID(study.getOc_oid());
        meta.setEffectiveDate(study.getDateCreated());
        // loc.setMetaDataVersionRef(meta);
        // data.getLocations().add(loc);
    }

    protected HashMap<String, String> getNullValueCVs(Study study) {
        HashMap<String, String> nullValueCVs = new HashMap<String, String>();
        int studyId = study.getStudyId();
        this.setNullValueCVsTypesExpected();
        ArrayList viewRows = new ArrayList();
        if (study.isSite()) {
            viewRows = select(this.getNullValueCVsSql(studyId + ""));
            if (viewRows.size() <= 0) {
                viewRows = select(this.getNullValueCVsSql(study.checkAndGetParentStudyId() + ""));
            }
        } else {
            viewRows = select(this.getNullValueCVsSql(studyId + ""));
        }
        Iterator iter = viewRows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            String sedOID = (String) row.get("definition_oid");
            String cvOID = (String) row.get("crf_version_oid");
            String nullValues = (String) row.get("null_values");
            nullValueCVs.put(studyId + "-" + sedOID + "-" + cvOID, nullValues);
        }
        return nullValueCVs;
    }

    // Gets Clinical Data for xml dataset extract
    public void getClinicalData(Study study, DatasetBean dataset, OdmClinicalDataBean data, String odmVersion, String studySubjectIds, String odmType, String permissionTagsString) {
        String dbName = CoreResources.getDBName();
        String subprev = "";
        HashMap<String, Integer> sepos = new HashMap<String, Integer>();
        String seprev = "";
        String formprev = "";
        HashMap<String, Integer> igpos = new HashMap<String, Integer>();
        String igprev = "";
        String oidPos = "";
        HashMap<Integer, String> oidPoses = new HashMap<Integer, String>();
        HashMap<Integer, String> idataOidPoses = new HashMap<Integer, String>();

        // String studyIds = "";
        // if (study.getParentStudyId() > 0) {
        // studyIds += study.getId();
        // } else {
        // ArrayList<Integer> ids = (ArrayList<Integer>) (new
        // StudyDAO(this.ds)).findAllSiteIdsByStudy(study);
        // for (int i = 0; i < ids.size() - 1; ++i) {
        // studyIds += ids.get(i) + ",";
        // }
        // studyIds += ids.get(ids.size() - 1);
        // }

        String studyIds = study.getStudyId() + "";
        int datasetItemStatusId = dataset.getDatasetItemStatus().getId();
        String sql = dataset.getSQLStatement().split("order by")[0].trim();
        sql = sql.split("study_event_definition_id in")[1];
        String[] ss = sql.split("and item_id in");
        String sedIds = ss[0];
        String[] sss = ss[1].split("and");
        String itemIds = sss[0];
        String dateConstraint = "";
        if ("postgres".equalsIgnoreCase(dbName)) {
            dateConstraint = "and " + sss[1] + " and " + sss[2];
            dateConstraint = dateConstraint.replace("date_created", "ss.enrollment_date");
        } else if ("oracle".equalsIgnoreCase(dbName)) {
            String[] os = (sss[1] + sss[2]).split("'");
            dateConstraint = "and trunc(ss.enrollment_date) >= to_date('" + os[1] + "') and trunc(ss.enrollment_date) <= to_date('" + os[3] + "')";
        }
        logger.debug("Begin to GetSubjectEventFormSql");
        if (odmVersion.startsWith("oc")) {
            logger.info(
                    "getOCSubjectEventFormSql=" + getOCSubjectEventFormSqlSS(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId, studySubjectIds));
            this.setSubjectEventFormDataTypesExpected(odmVersion);
            ArrayList viewRows = select(getOCSubjectEventFormSqlSS(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId, studySubjectIds));
            Iterator iter = viewRows.iterator();
            this.setDataWithOCAttributes(study, dataset, data, odmVersion, iter, oidPoses, odmType, permissionTagsString);
        } else {
            logger.info("getSubjectEventFormSql=" + getSubjectEventFormSqlSS(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId, studySubjectIds));
            this.setSubjectEventFormDataTypesExpected();
            ArrayList viewRows = select(getSubjectEventFormSqlSS(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId, studySubjectIds));
            Iterator iter = viewRows.iterator();
            // if any algorithm modification has been done in this block,
            // the method "setDataWithOCAttributes" should be checked about the
            // updated as well because this method came from here.
            while (iter.hasNext()) {
                JobTerminationMonitor.check();
                HashMap row = (HashMap) iter.next();
                String studySubjectLabel = (String) row.get("study_subject_oid");
                String sedOID = (String) row.get("definition_oid");
                Boolean studyEventRepeating = (Boolean) row.get("definition_repeating");
                Integer sampleOrdinal = (Integer) row.get("sample_ordinal");
                String cvOID = (String) row.get("crf_version_oid");
                Integer ecId = (Integer) row.get("event_crf_id");// ecId
                CRFVersionBean cvBean = cvdao.findByOid(cvOID);
                CRFBean cBean = (CRFBean) crfdao.findByPK(cvBean.getCrfId());


                // should
                // be unique;

                String key = studySubjectLabel;
                ExportSubjectDataBean sub = new ExportSubjectDataBean();
                if (subprev.equals(studySubjectLabel)) {
                    int p = data.getExportSubjectData().size() - 1;
                    sub = data.getExportSubjectData().get(p);
                } else {
                    subprev = studySubjectLabel;
                    sub.setSubjectOID(studySubjectLabel);
                    data.getExportSubjectData().add(sub);
                    seprev = "";
                    formprev = "";
                    igprev = "";
                }
                oidPos = data.getExportSubjectData().size() - 1 + "";
                ExportStudyEventDataBean se = new ExportStudyEventDataBean();
                // key += sedOID + sampleOrdinal;
                key += sedOID;
                if (!seprev.equals(key) || !sepos.containsKey(key + sampleOrdinal)) {
                    sepos.put(key + sampleOrdinal, sub.getExportStudyEventData().size());
                    seprev = key;
                    se.setStudyEventOID(sedOID);
                    se.setStudyEventRepeatKey(studyEventRepeating ? sampleOrdinal + "" : "-1");
                    sub.getExportStudyEventData().add(se);
                    formprev = "";
                    igprev = "";
                } else {
                    se = sub.getExportStudyEventData().get(sepos.get(key + sampleOrdinal));
                }
                oidPos += "---" + (sub.getExportStudyEventData().size() - 1);
                ExportFormDataBean form = new ExportFormDataBean();
                key += cvOID;
                if (formprev.equals(key)) {
                    form = se.getExportFormData().get(se.getExportFormData().size() - 1);
                } else {
                    formprev = key;
                    form.setFormOID(cBean.getOid());
                    se.getExportFormData().add(form);
                    igprev = "";
                }
                oidPos += "---" + (se.getExportFormData().size() - 1);
                // ecId should be distinct
                oidPoses.put(ecId, oidPos);
                oidPos = "";
            }
        }

        this.setEventGroupItemDataWithUnitTypesExpected();
        logger.debug("Begin to GetEventGroupItemWithUnitSql");
        ArrayList viewRows = select(getEventGroupItemWithUnitSql(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId, studySubjectIds));
        logger.info("getEventGroupItemWithUnitSql : "
                + getEventGroupItemWithUnitSql(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId, studySubjectIds));
        String idataIds = "";
        if (viewRows.size() > 0) {
            Iterator iter = viewRows.iterator();
            ExportSubjectDataBean sub = new ExportSubjectDataBean();
            ExportStudyEventDataBean se = new ExportStudyEventDataBean();
            ExportFormDataBean form = new ExportFormDataBean();
            int ecprev = -1;
            igprev = "";
            boolean goon = true;
            String itprev = "";
            // HashMap<String, String> nullValueMap =
            // ClinicalDataUnit.getNullValueMap();

            HashMap<String, String> nullValueCVs = this.getNullValueCVs(study);
            HashSet<Integer> itemDataIds = new HashSet<Integer>();
            String yearMonthFormat = StringUtil.parseDateFormat(ResourceBundleProvider.getFormatBundle(locale).getString("date_format_year_month"));
            String yearFormat = StringUtil.parseDateFormat(ResourceBundleProvider.getFormatBundle(locale).getString("date_format_year"));
            while (iter.hasNext()) {
                JobTerminationMonitor.check();
                HashMap row = (HashMap) iter.next();
                Integer ecId = (Integer) row.get("event_crf_id");
                Integer igId = (Integer) row.get("item_group_id");
                String igOID = (String) row.get("item_group_oid");
                String igName = (String) row.get("item_group_name");
                Integer itId = (Integer) row.get("item_id");
                String itOID = (String) row.get("item_oid");
                Integer itDataOrdinal = (Integer) row.get("item_data_ordinal");
                String itValue = (String) row.get("value");
                Integer datatypeid = (Integer) row.get("item_data_type_id");
                Integer idataId = (Integer) row.get("item_data_id");
                String itemName = (String) row.get("item_name");
                String muOid = (String) row.get("mu_oid");
                String key = "";
                boolean formIsTagged = false;
                if (ecId != ecprev) {
                    logger.debug("Found ecId=" + ecId + " in subjectEventFormSql is:" + oidPoses.containsKey(ecId));
                    EventCRFBean eventCRFBean = (EventCRFBean) ecdao.findByPK(ecId);
                    StudyEventBean studyEventBean = (StudyEventBean) sedao.findByPK(eventCRFBean.getStudyEventId());
                    CRFBean crfBean = crfdao.findByLayoutId(eventCRFBean.getFormLayoutId());

                    EventDefinitionCRFBean edc = edcdao.findByStudyEventDefinitionIdAndCRFId(study, studyEventBean.getStudyEventDefinitionId(), crfBean.getId());
                    //             List <EventDefinitionCrfPermissionTag> edcPTagIds= eventDefinitionCrfPermissionTagDao.findByEdcIdTagId(edc.getId(), edc.getParentId(),tagIds);


                    formIsTagged = isFormTagged(edc);
                    if (formIsTagged & permissionTagsString != null && permissionTagsString.length() > 0) {
                        formIsTagged = permissionAccessCheck(edc, permissionTagsString);
                    } else if (formIsTagged && (permissionTagsString == null || permissionTagsString.length() == 0)) {
                        ClinicalDataCollector.datasetFiltered = "YES";
                    }

                    if (oidPoses.containsKey(ecId) && !formIsTagged) {
                        goon = true;
                        String[] poses = oidPoses.get(ecId).split("---");
                        sub = data.getExportSubjectData().get(Integer.valueOf(poses[0]));
                        se = sub.getExportStudyEventData().get(Integer.valueOf(poses[1]));
                        form = se.getExportFormData().get(Integer.valueOf(poses[2]));
                    } else {
                        goon = false;
                    }
                    ecprev = ecId;
                }
                if (goon) {
                    ImportItemGroupDataBean ig = new ImportItemGroupDataBean();
                    // key = ecId + igOID;
                    key = ecId + "-" + igId;
                    if (!igprev.equals(key) || !igpos.containsKey(key + itDataOrdinal)) {
                        igpos.put(key + itDataOrdinal, form.getItemGroupData().size());
                        igprev = key;
                        ig.setItemGroupOID(igOID + "");
                        ig.setItemGroupName(igName);
                        ig.setItemGroupRepeatKey("ungrouped".equalsIgnoreCase(igName) ? "-1" : itDataOrdinal + "");
                        form.getItemGroupData().add(ig);
                    } else {
                        ig = form.getItemGroupData().get(igpos.get(key + itDataOrdinal));
                    }
                    String newpos = oidPoses.get(ecId) + "---" + igpos.get(key + itDataOrdinal);
                    // item should be distinct; but duplicated item data have
                    // been reported because "save" have been clicked twice.
                    // those duplicated item data have been arranged together by
                    // "distinct" because of their same
                    // ecId+igOID+itOID+itDataOrdinal (08-2008)
                    key = itId + "_" + itDataOrdinal + key;
                    if (!itprev.equals(key)) {
                        itprev = key;
                        ImportItemDataBean it = new ImportItemDataBean();
                        it.setItemOID(itOID);
                        it.setTransactionType("Insert");
                        it.setItemName(itemName);
                        String nullKey = study.getStudyId() + "-" + se.getStudyEventOID() + "-" + form.getFormOID();
                        if (ClinicalDataUtil.isNull(itValue, nullKey, nullValueCVs)) {
                            // if
                            // (nullValueMap.containsKey(itValue.trim().toUpperCase()))
                            // {
                            // itValue =
                            // nullValueMap.get(itValue.trim().toUpperCase());

                            it.setIsNull("Yes");
                            String nullvalues = ClinicalDataUtil.presetNullValueStr(nullValueCVs.get(nullKey));
                            boolean hasValueWithNull = ClinicalDataUtil.isValueWithNull(itValue, nullvalues);
                            it.setHasValueWithNull(hasValueWithNull);
                            if (hasValueWithNull) {
                                it.setValue(itValue);
                                it.setReasonForNull(ClinicalDataUtil.getNullsInValue(itValue, nullvalues));
                            } else {
                                it.setReasonForNull(itValue.trim());
                            }
                        } else {
                            if (datatypeid == 9) {
                                try {
                                    itValue = new SimpleDateFormat("yyyy-MM-dd").format(new SimpleDateFormat(oc_df_string).parse(itValue));
                                } catch (Exception fe) {
                                    logger.debug("Item -" + itOID + " value " + itValue + " might not be ODM date format yyyy-MM-dd.");
                                }
                            }
                            /*
                             * not be supported in openclinica-3.0.40.1
                             * else if (datatypeid == 10 && odmVersion.contains("1.3")) {
                             * if (StringUtil.isFormatDate(itValue, oc_df_string)) {
                             * try {
                             * itValue = new SimpleDateFormat("yyyy-MM-dd").format(new
                             * SimpleDateFormat(oc_df_string).parse(itValue));
                             * } catch (Exception e) {
                             * logger.info("Item -" + itOID + " value " + itValue +
                             * " might not be ODM partialDate format yyyy[-MM[-dd]].");
                             * }
                             * } else {
                             * if (StringUtil.isPartialYearMonth(itValue, yearMonthFormat)) {
                             * try {
                             * itValue = new SimpleDateFormat("yyyy-MM").format(new
                             * SimpleDateFormat(yearMonthFormat).parse(itValue));
                             * } catch (Exception e) {
                             * logger.info("Item -" + itOID + " value " + itValue +
                             * " might not be ODM partialDate format yyyy[-MM[-dd]].");
                             * }
                             * } else {
                             * try {
                             * itValue = new SimpleDateFormat("yyyy").format(new
                             * SimpleDateFormat(yearFormat).parse(itValue));
                             * } catch (Exception e) {
                             * logger.info("Item -" + itOID + " value " + itValue +
                             * " might not be ODM partialDate format yyyy[-MM[-dd]].");
                             * }
                             * }
                             * }
                             * }
                             */
                            it.setValue(itValue);
                        }
                        if (muOid != null && muOid.length() > 0) {
                            ElementRefBean measurementUnitRef = new ElementRefBean();
                            measurementUnitRef.setElementDefOID(muOid);
                            it.setMeasurementUnitRef(measurementUnitRef);
                        }
                        ig.getItemData().add(it);
                        newpos += "---" + (ig.getItemData().size() - 1);
                        idataOidPoses.put(idataId, newpos);
                    }
                    idataIds += "'" + idataId + "', ";
                }
            }
        }

        idataIds = idataIds.length() > 0 ? idataIds.substring(0, idataIds.length() - 2) : idataIds;
        if (idataIds.length() > 0 && itemIds.length() > 0) {
            this.setErasedScoreItemDataValues(data, itemIds, idataIds, idataOidPoses, odmVersion);
        } else {
            logger.info("OdmExtractDAO.setScoreItemDataNullValues was not called because of empty item_data_ids or/and item_ids");
        }

        if (odmType != null && odmType.equalsIgnoreCase("clinical_data")) {
            logger.debug("Do not create discrepancy notes");
        } else if (odmVersion.startsWith("oc")) {
            if (idataIds.length() > 0) {
                setOCItemDataAuditLogs(study, data, idataIds, idataOidPoses);
                setOCItemDataDNs(data, idataIds, idataOidPoses);
            } else {
                logger.info("OdmExtractDAO.setOCItemDataAuditLogs & setOCItemDataDNs weren't called because of empty idataIds");
            }
        }
    }

    public void getClinicalData(Study study, OdmClinicalDataBean data, String odmVersion, String studySubjectIds, String odmType) {

        String dbName = CoreResources.getDBName();
        String subprev = "";
        HashMap<String, Integer> sepos = new HashMap<String, Integer>();
        String seprev = "";
        String formprev = "";
        HashMap<String, Integer> igpos = new HashMap<String, Integer>();
        String igprev = "";
        String oidPos = "";
        HashMap<Integer, String> oidPoses = new HashMap<Integer, String>();
        HashMap<Integer, String> idataOidPoses = new HashMap<Integer, String>();
        String studyIds = study.getStudyId() + "";

    }

    protected void setErasedScoreItemDataValues(OdmClinicalDataBean data, String itemIds, String itemDataIds, HashMap<Integer, String> idataOidPoses,
                                                String odmVersion) {
        this.setErasedScoreItemDataIdsTypesExpected();
        ArrayList<Integer> rows = this.select(this.getErasedScoreItemDataIdsSql(itemIds, itemDataIds));
        if (rows == null || rows.size() < 1) {
            logger.debug("OdmExtractDAO.getErasedScoreItemDataIdsSql return no erased score item_data_id");
        } else {
            Iterator iter = rows.iterator();
            while (iter.hasNext()) {
                HashMap row = (HashMap) iter.next();
                Integer idataId = (Integer) row.get("item_data_id");
                if (idataOidPoses.containsKey(idataId)) {
                    String[] poses = idataOidPoses.get(idataId).split("---");
                    ImportItemDataBean idata = data.getExportSubjectData().get(Integer.parseInt(poses[0])).getExportStudyEventData()
                            .get(Integer.parseInt(poses[1])).getExportFormData().get(Integer.parseInt(poses[2])).getItemGroupData()
                            .get(Integer.parseInt(poses[3])).getItemData().get(Integer.parseInt(poses[4]));
                    idata.setIsNull("Yes");
                    idata.setValue("");
                    idata.setReasonForNull("Erased");
                } else {
                    logger.info("There is no erased score item data with item_data_id =" + idataId + " found in OdmClinicalData");
                }
            }
        }
    }

    protected void setOCSubjectDataAuditLogs(Study study, OdmClinicalDataBean data, String studySubjectOids, HashMap<String, String> subOidPoses) {
        this.setOCSubjectDataAuditsTypesExpected();
        logger.debug("Begin to execute GetOCSubjectDataAuditsSql");
        logger.debug("getOCSubjectDataAuditsSql= " + this.getOCSubjectDataAuditsSql(studySubjectOids));
        ArrayList rows = select(this.getOCSubjectDataAuditsSql(studySubjectOids));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            String studySubjectLabel = (String) row.get("study_subject_oid");
            Integer auditId = (Integer) row.get("audit_id");
            String type = (String) row.get("name");
            Integer userId = (Integer) row.get("user_id");
            Date auditDate = (Date) row.get("audit_date");
            String auditReason = (String) row.get("reason_for_change");
            String oldValue = (String) row.get("old_value");
            String newValue = (String) row.get("new_value");
            Integer typeId = (Integer) row.get("audit_log_event_type_id");

            if (subOidPoses.containsKey(studySubjectLabel)) {

                ExportSubjectDataBean sub = data.getExportSubjectData().get(Integer.parseInt(subOidPoses.get(studySubjectLabel)));
                AuditLogBean auditLog = new AuditLogBean();
                auditLog.setOid("AL_" + auditId);
                auditLog.setUserId("USR_" + userId);
                logger.debug("datatime=" + auditDate + " or " + new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss").format(auditDate));
                auditLog.setDatetimeStamp(auditDate);
                auditLog.setType(type);
                auditLog.setReasonForChange(auditReason);
                auditLog.setAuditLogEventTypeId(typeId);
                if (typeId == 3 || typeId == 6) {
                    if ("0".equals(newValue)) {
                        auditLog.setOldValue(Status.INVALID.getName());
                    } else {
                        auditLog.setNewValue(Status.getFromMap(Integer.parseInt(newValue)).getName());
                    }
                    if ("0".equals(oldValue)) {
                        auditLog.setOldValue(Status.INVALID.getName());
                    } else {
                        auditLog.setOldValue(Status.getFromMap(Integer.parseInt(oldValue)).getName());
                    }
                } else {
                    auditLog.setNewValue(newValue);
                    auditLog.setOldValue(oldValue);
                }
                AuditLogsBean logs = sub.getAuditLogs();
                if (logs.getEntityID() == null || logs.getEntityID().length() <= 0) {
                    logs.setEntityID(sub.getSubjectOID());
                }
                logs.getAuditLogs().add(auditLog);
                sub.setAuditLogs(logs);
            }
        }
    }

    protected void setOCEventDataAuditLogs(Study study, OdmClinicalDataBean data, String studySubjectOids, HashMap<String, String> evnOidPoses) {
        this.setOCEventDataAuditsTypesExpected();
        logger.debug("Begin to execute GetOCEventDataAuditsSql");
        logger.debug("getOCEventDataAuditsSql= " + this.getOCEventDataAuditsSql(studySubjectOids));
        ArrayList rows = select(this.getOCEventDataAuditsSql(studySubjectOids));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            String studySubjectLabel = (String) row.get("study_subject_oid");
            String sedOid = (String) row.get("definition_oid");
            Integer auditId = (Integer) row.get("audit_id");
            String type = (String) row.get("name");
            Integer userId = (Integer) row.get("user_id");
            Date auditDate = (Date) row.get("audit_date");
            String entity_name = (String) row.get("entity_name");
            String auditReason = (String) row.get("reason_for_change");
            String oldValue = (String) row.get("old_value");
            String newValue = (String) row.get("new_value");
            String details = (String) row.get("details");
            Integer typeId = (Integer) row.get("audit_log_event_type_id");
            Integer eventRepeat = (Integer) row.get("event_repeat");


            if (evnOidPoses.containsKey(studySubjectLabel + sedOid)) {
                String[] poses = evnOidPoses.get(studySubjectLabel + sedOid).split("---");
                List<ExportStudyEventDataBean> exportStudyEventDataBeans = (List<ExportStudyEventDataBean>) data.getExportSubjectData().get(Integer.parseInt(poses[0])).getExportStudyEventData();
                ExportStudyEventDataBean se = null;
                for (ExportStudyEventDataBean exportStudyEventDataBean : exportStudyEventDataBeans) {
                    int eventRepeatNumber = Integer.valueOf(exportStudyEventDataBean.getStudyEventRepeatKey());
                    if (exportStudyEventDataBean.getStudyEventOID().equals(sedOid) && eventRepeatNumber == eventRepeat) {
                        se = exportStudyEventDataBean;
                        break;
                    }
                }

                if (se != null) {
                    AuditLogBean auditLog = new AuditLogBean();
                    auditLog.setOid("AL_" + auditId);
                    auditLog.setUserId("USR_" + userId);
                    auditLog.setDatetimeStamp(auditDate);
                    auditLog.setType(type);
                    auditLog.setReasonForChange(auditReason);
                    auditLog.setDetails(details);
                    auditLog.setAuditLogEventTypeId(typeId);
                    auditLog.setValueType(entity_name);

                    if (entity_name.equals("Status")) {
                        auditLog.setOldValue(setStudyEventStatus(oldValue));
                        auditLog.setNewValue(setStudyEventStatus(newValue));
                    } else {
                        auditLog.setOldValue(oldValue);
                        auditLog.setNewValue(newValue);
                    }


                    AuditLogsBean logs = se.getAuditLogs();
                    if (logs.getEntityID() == null || logs.getEntityID().length() <= 0) {
                        logs.setEntityID(se.getStudyEventOID());
                    }
                    logs.getAuditLogs().add(auditLog);
                    se.setAuditLogs(logs);
                }
            }
        }
    }

    protected void setOCFormDataAuditLogs(Study study, OdmClinicalDataBean data, String studySubjectOids, String ecIds,
                                          HashMap<Integer, String> formOidPoses) {
        this.setOCFormDataAuditsTypesExpected();
        String dbName = CoreResources.getDBName();
        logger.debug("Begin to execute GetOCFormDataAuditsSql");
        logger.debug("getOCFormDataAuditsSql= " + this.getOCFormDataAuditsSql(studySubjectOids, ecIds));
        ArrayList rows = select(this.getOCFormDataAuditsSql(studySubjectOids, ecIds));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            Integer ecId = (Integer) row.get("event_crf_id");
            Integer auditId = (Integer) row.get("audit_id");
            String type = (String) row.get("name");
            Integer userId = (Integer) row.get("user_id");
            Date auditDate = (Date) row.get("audit_date");
            String entity_name = (String) row.get("entity_name");
            String auditReason = (String) row.get("reason_for_change");
            String oldValue = (String) row.get("old_value");
            String newValue = (String) row.get("new_value");
            Integer typeId = (Integer) row.get("audit_log_event_type_id");

            if (formOidPoses.containsKey(ecId)) {
                String[] poses = formOidPoses.get(ecId).split("---");
                ExportFormDataBean form = data.getExportSubjectData().get(Integer.parseInt(poses[0])).getExportStudyEventData().get(Integer.parseInt(poses[1]))
                        .getExportFormData().get(Integer.parseInt(poses[2]));
                AuditLogBean auditLog = new AuditLogBean();
                auditLog.setOid("AL_" + auditId);
                auditLog.setUserId("USR_" + userId);
                auditLog.setDatetimeStamp(auditDate);
                auditLog.setType(type);
                auditLog.setReasonForChange(auditReason);
                auditLog.setAuditLogEventTypeId(typeId);
                auditLog.setValueType(entity_name);

                if (entity_name.equals("Status")) {
                    auditLog.setOldValue(setEventCrfStatus(oldValue));
                    auditLog.setNewValue(setEventCrfStatus(newValue));
                } else {
                    auditLog.setOldValue(oldValue);
                    auditLog.setNewValue(newValue);
                }

                // Fix for 0011675: SDV'ed subject is dipslayed as not SDV'ed in the 1.3 Full ODM Extract commenting
                // out the following lines as these are treated like booleans while they are strings
                // JN:The Oracle still continues to have 1 and 2 for this audit type 32 so enabling the following code
                // for oracle only, ideally the trigger should be coded same for both postgres and oracle and since
                // the trigger doesnt do same things, the existing data would still be a problem, so doing this patch
                // work

                AuditLogsBean logs = form.getAuditLogs();
                if (logs.getEntityID() == null || logs.getEntityID().length() <= 0) {
                    logs.setEntityID(form.getFormOID());
                }
                logs.getAuditLogs().add(auditLog);
                form.setAuditLogs(logs);
            }
        }
    }

    protected void setOCItemDataAuditLogs(Study study, OdmClinicalDataBean data, String idataIds, HashMap<Integer, String> idataOidPoses) {
        this.setOCItemDataAuditsTypesExpected();
        logger.debug("Begin to execute GetOCItemDataAuditsSql");
        logger.debug("getOCItemDataAuditsSql= " + this.getOCItemDataAuditsSql(idataIds));
        ArrayList rows = select(this.getOCItemDataAuditsSql(idataIds));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            Integer idataId = (Integer) row.get("item_data_id");
            Integer auditId = (Integer) row.get("audit_id");
            String type = (String) row.get("name");
            Integer userId = (Integer) row.get("user_id");
            Date auditDate = (Date) row.get("audit_date");
            String auditReason = (String) row.get("reason_for_change");
            String oldValue = (String) row.get("old_value");
            String newValue = (String) row.get("new_value");
            Integer typeId = (Integer) row.get("audit_log_event_type_id");

            if (idataOidPoses.containsKey(idataId)) {
                String[] poses = idataOidPoses.get(idataId).split("---");
                ImportItemDataBean idata = data.getExportSubjectData().get(Integer.parseInt(poses[0])).getExportStudyEventData().get(Integer.parseInt(poses[1]))
                        .getExportFormData().get(Integer.parseInt(poses[2])).getItemGroupData().get(Integer.parseInt(poses[3])).getItemData()
                        .get(Integer.parseInt(poses[4]));
                AuditLogBean auditLog = new AuditLogBean();
                auditLog.setOid("AL_" + auditId);
                auditLog.setUserId("USR_" + userId);
                auditLog.setDatetimeStamp(auditDate);
                auditLog.setType(type);
                auditLog.setReasonForChange(auditReason);
                auditLog.setAuditLogEventTypeId(typeId);
                if (typeId == 12) {
                    if ("0".equals(newValue)) {
                        auditLog.setOldValue(Status.INVALID.getName());
                    } else {
                        auditLog.setNewValue(Status.getFromMap(Integer.parseInt(newValue)).getName());
                    }
                    if ("0".equals(oldValue)) {
                        auditLog.setOldValue(Status.INVALID.getName());
                    } else {
                        auditLog.setOldValue(Status.getFromMap(Integer.parseInt(oldValue)).getName());
                    }
                } else {
                    auditLog.setNewValue(newValue);
                    auditLog.setOldValue(oldValue);
                }
                AuditLogsBean logs = idata.getAuditLogs();
                if (logs.getEntityID() == null || logs.getEntityID().length() <= 0) {
                    logs.setEntityID(idata.getItemOID());
                }
                logs.getAuditLogs().add(auditLog);
                idata.setAuditLogs(logs);
            }
        }
    }

    protected void setOCSubjectDataDNs(OdmClinicalDataBean data, String studySubjectOids, HashMap<String, String> subOidPoses) {
        this.setOCSubjectDataDNsTypesExpected();
        HashMap<String, ArrayList<ChildNoteBean>> pDNs = new HashMap<String, ArrayList<ChildNoteBean>>();
        // HashMap<String, ArrayList<DiscrepancyNoteBean>> sDNs = new HashMap<String, ArrayList<DiscrepancyNoteBean>>();
        logger.debug("Begin to execute GetOCSubjectDataDNsSql");
        logger.debug("getOCSubjectDataDNsSql= " + this.getOCSubjectDataDNsSql(studySubjectOids));
        ArrayList rows = select(this.getOCSubjectDataDNsSql(studySubjectOids));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            String studySubjectLabel = (String) row.get("study_subject_oid");
            Integer pdnId = (Integer) row.get("parent_dn_id");
            Integer dnId = (Integer) row.get("dn_id");
            String description = (String) row.get("description");
            String detailedNote = (String) row.get("detailed_notes");
            Integer ownerId = (Integer) row.get("owner_id");
            Date dateCreated = (Date) row.get("date_created");
            String status = (String) row.get("status");
            String noteType = (String) row.get("name");
            String threadUuid = (String) row.get("thread_uuid");
            Integer threadNumber = (Integer) row.get("thread_number");

            if (pdnId != null && pdnId > 0) {
                String key = studySubjectLabel + "-" + pdnId;
                ChildNoteBean cn = new ChildNoteBean();
                cn.setDateCreated(dateCreated);
                cn.setDescription(description);
                cn.setDetailedNote(detailedNote);
                cn.setStatus(status);
                cn.setOid("CDN_" + dnId);
                ElementRefBean userRef = new ElementRefBean();
                userRef.setElementDefOID("USR_" + ownerId);
                cn.setUserRef(userRef);
                ArrayList<ChildNoteBean> cns = pDNs.containsKey(key) ? pDNs.get(key) : new ArrayList<ChildNoteBean>();
                cns.add(cn);
                pDNs.put(key, cns);
            } else {
                DiscrepancyNoteBean dn = new DiscrepancyNoteBean();
                String k = studySubjectLabel + "-" + dnId;
                if (pDNs != null && pDNs.containsKey(k)) {
                    dn.setChildNotes(pDNs.get(k));
                    dn.setNumberOfChildNotes(dn.getChildNotes().size());
                }
                dn.setDateUpdated(dateCreated);
                dn.setNoteType(noteType);
                dn.setStatus(status);
                dn.setOid("DN_" + dnId);
                dn.setThreadUuid(threadUuid);
                dn.setThreadNumber(threadNumber);
                ElementRefBean userRef = new ElementRefBean();
                userRef.setElementDefOID("USR_" + ownerId);
                if (subOidPoses.containsKey(studySubjectLabel)) {
                    int i = Integer.parseInt(subOidPoses.get(studySubjectLabel));
                    String entityID = data.getExportSubjectData().get(i).getSubjectOID();
                    data.getExportSubjectData().get(i).getDiscrepancyNotes().setEntityID(entityID);
                    data.getExportSubjectData().get(i).getDiscrepancyNotes().getDiscrepancyNotes().add(dn);
                }
            }
        }
    }

    protected void setOCEventDataDNs(OdmClinicalDataBean data, String definitionOids, String studySubjectOids, HashMap<String, String> evnOidPoses) {
        this.setOCEventDataDNsTypesExpected();
        HashMap<String, ArrayList<ChildNoteBean>> pDNs = new HashMap<String, ArrayList<ChildNoteBean>>();
        // HashMap<String, ArrayList<DiscrepancyNoteBean>> sDNs = new HashMap<String, ArrayList<DiscrepancyNoteBean>>();
        logger.debug("Begin to execute GetOCEventDataDNsSql");
        logger.debug("getOCEventDataDNsSql= " + this.getOCEventDataDNsSql(definitionOids, studySubjectOids));
        ArrayList rows = select(this.getOCEventDataDNsSql(definitionOids, studySubjectOids));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            String studySubjectLabel = (String) row.get("study_subject_oid");
            String defOid = (String) row.get("definition_oid");
            Integer pdnId = (Integer) row.get("parent_dn_id");
            Integer dnId = (Integer) row.get("dn_id");
            String description = (String) row.get("description");
            String detailedNote = (String) row.get("detailed_notes");
            Integer ownerId = (Integer) row.get("owner_id");
            Date dateCreated = (Date) row.get("date_created");
            String status = (String) row.get("status");
            String noteType = (String) row.get("name");
            String threadUuid = (String) row.get("thread_uuid");
            Integer threadNumber = (Integer) row.get("thread_number");

            String oidKey = studySubjectLabel + defOid;
            if (pdnId != null && pdnId > 0) {
                String key = oidKey + pdnId;
                ChildNoteBean cn = new ChildNoteBean();
                cn.setDateCreated(dateCreated);
                cn.setDescription(description);
                cn.setDetailedNote(detailedNote);
                cn.setStatus(status);
                cn.setOid("CDN_" + dnId);
                ElementRefBean userRef = new ElementRefBean();
                userRef.setElementDefOID("USR_" + ownerId);
                cn.setUserRef(userRef);
                ArrayList<ChildNoteBean> cns = pDNs.containsKey(key) ? pDNs.get(key) : new ArrayList<ChildNoteBean>();
                cns.add(cn);
                pDNs.put(key, cns);
            } else {
                DiscrepancyNoteBean dn = new DiscrepancyNoteBean();
                String k = oidKey + dnId;
                if (pDNs != null && pDNs.containsKey(k)) {
                    dn.setChildNotes(pDNs.get(k));
                    dn.setNumberOfChildNotes(dn.getChildNotes().size());
                }
                dn.setDateUpdated(dateCreated);
                dn.setNoteType(noteType);
                dn.setStatus(status);
                dn.setOid("DN_" + dnId);
                dn.setThreadUuid(threadUuid);
                dn.setThreadNumber(threadNumber);
                ElementRefBean userRef = new ElementRefBean();
                userRef.setElementDefOID("USR_" + ownerId);
                if (evnOidPoses.containsKey(oidKey)) {
                    String[] poses = evnOidPoses.get(oidKey).split("---");
                    int p0 = Integer.parseInt(poses[0]);
                    int p1 = Integer.parseInt(poses[1]);
                    String entityID = data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getStudyEventOID();
                    data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getDiscrepancyNotes().setEntityID(entityID);
                    data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getDiscrepancyNotes().getDiscrepancyNotes().add(dn);
                }
            }
        }
    }

    protected void setOCFormDataDNs(OdmClinicalDataBean data, String ecIds, HashMap<Integer, String> formOidPoses) {
        this.setOCFormDataDNsTypesExpected();
        HashMap<String, ArrayList<ChildNoteBean>> pDNs = new HashMap<String, ArrayList<ChildNoteBean>>();
        // HashMap<String, ArrayList<DiscrepancyNoteBean>> sDNs = new HashMap<String, ArrayList<DiscrepancyNoteBean>>();
        logger.debug("Begin to execute GetOCEventDataDNsSql");
        logger.debug("getOCFormDataDNsSql= " + this.getOCFormDataDNsSql(ecIds));
        ArrayList rows = select(this.getOCFormDataDNsSql(ecIds));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            Integer ecId = (Integer) row.get("event_crf_id");
            Integer pdnId = (Integer) row.get("parent_dn_id");
            Integer dnId = (Integer) row.get("dn_id");
            String description = (String) row.get("description");
            String detailedNote = (String) row.get("detailed_notes");
            Integer ownerId = (Integer) row.get("owner_id");
            Date dateCreated = (Date) row.get("date_created");
            String status = (String) row.get("status");
            String noteType = (String) row.get("name");
            String threadUuid = (String) row.get("thread_uuid");
            Integer threadNumber = (Integer) row.get("thread_number");

            if (pdnId != null && pdnId > 0) {
                String key = ecId + "-" + pdnId;
                ChildNoteBean cn = new ChildNoteBean();
                cn.setDateCreated(dateCreated);
                cn.setDescription(description);
                cn.setDetailedNote(detailedNote);
                cn.setStatus(status);
                cn.setOid("CDN_" + dnId);
                ElementRefBean userRef = new ElementRefBean();
                userRef.setElementDefOID("USR_" + ownerId);
                cn.setUserRef(userRef);
                ArrayList<ChildNoteBean> cns = pDNs.containsKey(key) ? pDNs.get(key) : new ArrayList<ChildNoteBean>();
                cns.add(cn);
                pDNs.put(key, cns);
            } else {
                DiscrepancyNoteBean dn = new DiscrepancyNoteBean();
                String k = ecId + "-" + dnId;
                if (pDNs != null && pDNs.containsKey(k)) {
                    dn.setChildNotes(pDNs.get(k));
                    dn.setNumberOfChildNotes(dn.getChildNotes().size());
                }
                dn.setDateUpdated(dateCreated);
                dn.setNoteType(noteType);
                dn.setStatus(status);
                dn.setOid("DN_" + dnId);
                dn.setThreadUuid(threadUuid);
                dn.setThreadNumber(threadNumber);
                ElementRefBean userRef = new ElementRefBean();
                userRef.setElementDefOID("USR_" + ownerId);
                if (formOidPoses.containsKey(ecId)) {
                    String[] poses = formOidPoses.get(ecId).split("---");
                    int p0 = Integer.parseInt(poses[0]);
                    int p1 = Integer.parseInt(poses[1]);
                    int p2 = Integer.parseInt(poses[2]);
                    String entityID = data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getExportFormData().get(p2).getFormOID();
                    data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getExportFormData().get(p2).getDiscrepancyNotes()
                            .setEntityID(entityID);
                    data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getExportFormData().get(p2).getDiscrepancyNotes()
                            .getDiscrepancyNotes().add(dn);
                }
            }
        }
    }

    protected void setOCItemDataDNs(OdmClinicalDataBean data, String idataIds, HashMap<Integer, String> idataOidPoses) {
        this.setOCItemDataDNsTypesExpected();
        HashMap<String, ArrayList<ChildNoteBean>> pDNs = new HashMap<String, ArrayList<ChildNoteBean>>();
        // HashMap<String, ArrayList<DiscrepancyNoteBean>> sDNs = new HashMap<String, ArrayList<DiscrepancyNoteBean>>();
        logger.debug("Begin to execute GetOCItemDataDNsSql");
        logger.debug("getOCItemDataDNsSql= " + this.getOCItemDataDNsSql(idataIds));
        ArrayList rows = select(this.getOCItemDataDNsSql(idataIds));
        Iterator iter = rows.iterator();
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            Integer idataId = (Integer) row.get("item_data_id");
            Integer pdnId = (Integer) row.get("parent_dn_id");
            Integer dnId = (Integer) row.get("dn_id");
            String description = (String) row.get("description");
            String detailedNote = (String) row.get("detailed_notes");
            Integer ownerId = (Integer) row.get("owner_id");
            Date dateCreated = (Date) row.get("date_created");
            String status = (String) row.get("status");
            String noteType = (String) row.get("name");
            String threadUuid = (String) row.get("thread_uuid");
            Integer threadNumber = (Integer) row.get("thread_number");

            if (pdnId != null && pdnId > 0) {
                String key = idataId + "-" + pdnId;
                ChildNoteBean cn = new ChildNoteBean();
                cn.setDateCreated(dateCreated);
                cn.setDescription(description);
                cn.setDetailedNote(detailedNote);
                cn.setStatus(status);
                cn.setOid("CDN_" + dnId);
                ElementRefBean userRef = new ElementRefBean();
                userRef.setElementDefOID("USR_" + ownerId);
                cn.setUserRef(userRef);
                ArrayList<ChildNoteBean> cns = pDNs.containsKey(key) ? pDNs.get(key) : new ArrayList<ChildNoteBean>();
                cns.add(cn);
                pDNs.put(key, cns);
            } else {
                DiscrepancyNoteBean dn = new DiscrepancyNoteBean();
                String k = idataId + "-" + dnId;
                if (pDNs != null && pDNs.containsKey(k)) {
                    dn.setChildNotes(pDNs.get(k));
                    dn.setNumberOfChildNotes(dn.getChildNotes().size());
                }
                dn.setDateUpdated(dateCreated);
                dn.setNoteType(noteType);
                dn.setStatus(status);
                dn.setOid("DN_" + dnId);
                dn.setThreadUuid(threadUuid);
                dn.setThreadNumber(threadNumber);

                ElementRefBean userRef = new ElementRefBean();
                userRef.setElementDefOID("USR_" + ownerId);
                if (idataOidPoses.containsKey(idataId)) {
                    String[] poses = idataOidPoses.get(idataId).split("---");
                    int p0 = Integer.parseInt(poses[0]);
                    int p1 = Integer.parseInt(poses[1]);
                    int p2 = Integer.parseInt(poses[2]);
                    int p3 = Integer.parseInt(poses[3]);
                    int p4 = Integer.parseInt(poses[4]);
                    String entityID = data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getExportFormData().get(p2).getItemGroupData()
                            .get(p3).getItemData().get(p4).getItemOID();
                    data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getExportFormData().get(p2).getItemGroupData().get(p3).getItemData()
                            .get(p4).getDiscrepancyNotes().setEntityID(entityID);
                    data.getExportSubjectData().get(p0).getExportStudyEventData().get(p1).getExportFormData().get(p2).getItemGroupData().get(p3).getItemData()
                            .get(p4).getDiscrepancyNotes().getDiscrepancyNotes().add(dn);
                }
            }
        }
    }

    protected void setDataWithOCAttributes(Study study, DatasetBean dataset, OdmClinicalDataBean data, String odmVersion, Iterator iter,
                                           HashMap<Integer, String> oidPoses, String odmType, String permissionTagsString) {
        String subprev = "";
        HashMap<String, Integer> sepos = new HashMap<String, Integer>();
        String seprev = "";
        String formprev = "";
        HashMap<String, Integer> igpos = new HashMap<String, Integer>();
        String igprev = "";
        String oidPos = "";
        Study parentStudy = study.isSite() ? (Study) study.getStudy() : study;
        setStudyParemeterConfig(parentStudy);
        HashSet<Integer> sgcIdSet = new HashSet<Integer>();
        HashMap<String, String> subOidPoses = new HashMap<String, String>();
        HashMap<String, String> evnOidPoses = new HashMap<String, String>();
        String studySubjectOids = "";
        String sedOids = "";
        String ecIds = "";
        while (iter.hasNext()) {
            HashMap row = (HashMap) iter.next();
            String studySubjectLabel = (String) row.get("study_subject_oid");
            // String label = (String) row.get("label");
            Integer sgcId = (Integer) row.get("sgc_id");
            String sgcName = (String) row.get("sgc_name");
            String sgName = (String) row.get("sg_name");
            String sedName = (String) row.get("definition_name");
            String sedOID = (String) row.get("definition_oid");
            Boolean studyEventRepeating = (Boolean) row.get("definition_repeating");
            Integer sampleOrdinal = (Integer) row.get("sample_ordinal");
            Date startDate = (Date) row.get("date_start");
            String flOID = (String) row.get("form_layout_oid");

            Integer ecId = (Integer) row.get("event_crf_id");// ecId should
            // be unique;
            Date dob = (Date) row.get("date_of_birth");
            FormLayoutBean flBean = fldao.findByOid(flOID);

            CRFBean cBean = (CRFBean) crfdao.findByPK(flBean.getCrfId());
            Integer edcId = (Integer) row.get("edc_id");// ecId

            boolean formIsTagged = false;
            boolean sedIsTagged = false;

            EventDefinitionCRFBean edc = (EventDefinitionCRFBean) edcdao.findByPK(edcId);
            StudyEventDefinitionBean sed = seddao.findByOid(sedOID);

            formIsTagged = isFormTagged(edc);
            if (formIsTagged & permissionTagsString != null && permissionTagsString.length() > 0) {
                formIsTagged = permissionAccessCheck(edc, permissionTagsString);
            } else if (formIsTagged && (permissionTagsString == null || permissionTagsString.length() == 0)) {
                ClinicalDataCollector.datasetFiltered = "YES";
            }

            if (formIsTagged && sed.getType().equals("common")) {
                sedIsTagged = true;
            }

            String key = studySubjectLabel;
            ExportSubjectDataBean sub = new ExportSubjectDataBean();
            if (subprev.equals(studySubjectLabel)) {
                int p = data.getExportSubjectData().size() - 1;
                sub = data.getExportSubjectData().get(p);
                // ------ add openclinica subject_group
                if (sgcId > 0) {
                    int presize = sgcIdSet.size();
                    sgcIdSet.add(sgcId);
                    if (sgcIdSet.size() > presize) {
                        sgcIdSet.add(sgcId);
                        SubjectGroupDataBean sgd = new SubjectGroupDataBean();
                        sgd.setStudyGroupClassId("SGC_" + sgcId);
                        sgd.setStudyGroupClassName(sgcName);
                        sgd.setStudyGroupName(sgName);
                        sub.getSubjectGroupData().add(sgd);
                    }
                }
                // ------ finish adding openclinica subject_group
            } else {
                subprev = studySubjectLabel;
                studySubjectOids += "'" + studySubjectLabel + "', ";
                sub.setSubjectOID(studySubjectLabel);
                // ----- add openclinica subject attributes
                sub.setStudySubjectId((String) row.get("label"));
                if (dataset.isShowSubjectUniqueIdentifier()) {
                    sub.setUniqueIdentifier((String) row.get("unique_identifier"));
                }
                if (dataset.isShowSubjectSecondaryId()) {
                    sub.setSecondaryId((String) row.get("secondary_label"));
                }
                if (dob != null) {
                    if (dataset.isShowSubjectDob()) {
                        if (parentStudy.getCollectDob().equals("2")) {
                            Calendar cal = Calendar.getInstance();
                            cal.setTime(dob);
                            int year = cal.get(Calendar.YEAR);
                            if (year > 0) {
                                sub.setYearOfBirth(year);
                            }
                        } else {
                            sub.setDateOfBirth(new SimpleDateFormat("yyyy-MM-dd").format(dob));
                        }
                    }
                }
                if (dataset.isShowSubjectGender()) {
                    sub.setSubjectGender((String) row.get("gender"));
                }
                if (dataset.isShowSubjectStatus()) {
                    sub.setStatus(Status.get((Integer) row.get("status_id")).getName());
                }
                // ------ finish adding openclinica subject attributes

                // ------ add openclinica subject_group
                if (sgcId > 0) {
                    sgcIdSet.clear();
                    sgcIdSet.add(sgcId);
                    SubjectGroupDataBean sgd = new SubjectGroupDataBean();
                    sgd.setStudyGroupClassId("SGC_" + sgcId);
                    sgd.setStudyGroupClassName(sgcName);
                    sgd.setStudyGroupName(sgName);
                    sub.getSubjectGroupData().add(sgd);
                }
                // ------ finish adding openclinica subject_group

                data.getExportSubjectData().add(sub);
                seprev = "";
                formprev = "";
                igprev = "";
            }


            oidPos = data.getExportSubjectData().size() - 1 + "";
            subOidPoses.put(studySubjectLabel, oidPos);


            if (!sedIsTagged) {
                ExportStudyEventDataBean se = new ExportStudyEventDataBean();
                // key += sedOID + sampleOrdinal;
                key += sedOID;
                if (!seprev.equals(key) || !sepos.containsKey(key + sampleOrdinal)) {
                    sepos.put(key + sampleOrdinal, sub.getExportStudyEventData().size());
                    seprev = key;
                    sedOids += "'" + sedOID + "', ";
                    se.setStudyEventOID(sedOID);
                    se.setEventName(sedName);
                    // ----- add openclinica study event attributes
                    if (startDate != null && dataset.isShowSubjectAgeAtEvent() && dob != null) {
                        se.setAgeAtEvent(Utils.getAge(dob, startDate));
                    }
                    if (dataset.isShowEventLocation()) {
                        se.setLocation((String) row.get("se_location"));
                    }
                    if (dataset.isShowEventStart() && startDate != null) {
                        if ((Boolean) row.get("start_time_flag") == Boolean.TRUE) {
                            se.setStartDate(new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss").format(startDate));
                        } else {
                            se.setStartDate(new SimpleDateFormat("yyyy-MM-dd").format(startDate));
                        }
                    }
                    Date endDate = (Date) row.get("date_end");
                    if (dataset.isShowEventEnd() && endDate != null) {
                        if ((Boolean) row.get("end_time_flag") == Boolean.TRUE) {
                            se.setEndDate(new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss").format(endDate));
                        } else {
                            se.setEndDate(new SimpleDateFormat("yyyy-MM-dd").format(endDate));
                        }
                    }
                    if (dataset.isShowEventStatus()) {
                        StudyEventWorkflowStatusEnum workflowEnum = StudyEventWorkflowStatusEnum.valueOf((String) row.get("event_workflow_status"));
                        se.setWorkflowStatus(workflowEnum);
                        Boolean event_removed = (Boolean) row.get("event_removed");
                        Boolean event_archived = (Boolean) row.get("event_archived");
                        Boolean event_locked = (Boolean) row.get("event_locked");
                        Boolean event_signed = (Boolean) row.get("event_signed");
                        se.setRemoved(event_removed);
                        se.setArchived(event_archived);
                        se.setLocked(event_locked);
                        se.setSigned(event_signed);


                    }
                    // ----- finish adding study event attributes
                    se.setStudyEventRepeatKey(studyEventRepeating ? sampleOrdinal + "" : "1");
                    sub.getExportStudyEventData().add(se);
                    formprev = "";
                    igprev = "";
                } else {
                    se = sub.getExportStudyEventData().get(sepos.get(key + sampleOrdinal));
                }
                oidPos += "---" + (sub.getExportStudyEventData().size() - 1);
                evnOidPoses.put(key, oidPos);

                if (!formIsTagged) {
                    ExportFormDataBean form = new ExportFormDataBean();
                    key += flOID;
                    if (formprev.equals(key)) {
                        form = se.getExportFormData().get(se.getExportFormData().size() - 1);
                    } else {
                        formprev = key;
                        ecIds += "'" + ecId + "', ";
                        form.setFormOID(cBean.getOid());
                        form.setFormName(cBean.getName());
                        form.setFormLayoutName(flBean.getName());
                        // ----- add openclinica crf attributes
                        if (dataset.isShowCRFversion()) {
                            form.setCrfVersion((String) row.get("crf_version"));
                        }
                        if (dataset.isShowCRFstatus()) {
                            EventCrfWorkflowStatusEnum workflowEnum = EventCrfWorkflowStatusEnum.valueOf((String) row.get("event_crf_workflow_status"));
                            form.setWorkflowStatus(workflowEnum);
                            Boolean form_removed = (Boolean) row.get("form_removed");
                            Boolean form_archived = (Boolean) row.get("form_archived");

                            form.setRemoved(form_removed);
                            form.setArchived(form_archived);

                        }
                        if (row.get("sdv_status") != null && !row.get("sdv_status").equals("")) {
                            form.setSdvStatus(SdvStatus.valueOf((String) row.get("sdv_status")));
                        }
                        if (dataset.isShowCRFinterviewerName()) {
                            form.setInterviewerName((String) row.get("interviewer_name"));
                        }
                        if (dataset.isShowCRFinterviewerDate()) {
                            try {
                                form.setInterviewDate(new SimpleDateFormat("yyyy-MM-dd").format((Date) row.get("date_interviewed")));
                            } catch (NullPointerException npe) {
                                logger.debug("caught NPE for interviewDate");
                                // Comment it out for: 11592. For this exaction function, interviewDate should be kept as the
                                // same as in database.
                                // form.setInterviewDate(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
                            }
                        }
                        // ----- finish adding crf attributes
                        se.getExportFormData().add(form);
                        igprev = "";
                    }
                    oidPos += "---" + (se.getExportFormData().size() - 1);
                    // ecId should be distinct
                    oidPoses.put(ecId, oidPos);
                    oidPos = "";
                }
            }
        }
        studySubjectOids = studySubjectOids.length() > 0 ? studySubjectOids.substring(0, studySubjectOids.length() - 2).trim() : studySubjectOids;
        sedOids = sedOids.length() > 0 ? sedOids.substring(0, sedOids.length() - 2).trim() : sedOids;
        ecIds = ecIds.length() > 0 ? ecIds.substring(0, ecIds.length() - 2).trim() : ecIds;

        if (odmType != null && odmType.equalsIgnoreCase("clinical_data")) {
            logger.debug("No Audit logs or discrepancy Notes");
        } else {
            if (studySubjectOids.length() > 0) {
                this.setOCSubjectDataAuditLogs(parentStudy, data, studySubjectOids, subOidPoses);
                this.setOCEventDataAuditLogs(parentStudy, data, studySubjectOids, evnOidPoses);
                if (ecIds.length() > 0) {
                    this.setOCFormDataAuditLogs(parentStudy, data, studySubjectOids, ecIds, oidPoses);
                } else {
                    logger.debug("OdmExtractDAO.setOCFormDataAuditLogs wasn't called because of empty ecIds");
                }
                this.setOCSubjectDataDNs(data, studySubjectOids, subOidPoses);
                if (sedOids.length() > 0) {
                    this.setOCEventDataDNs(data, sedOids, studySubjectOids, evnOidPoses);
                } else {
                    logger.info("OdmExtractDAO.setOCEventDataDNs wasn't called because of empty sedOids");
                }
            } else {
                logger.debug("OdmExtractDAO methods(setOCSubjectDataAuditLogs,setOCEventDataAuditLogs,setOCFormDataAuditLogs,"
                        + "setOCSubjectDataDNs,setOCEventDataDNs) weren't called because of empty studySubjectOids");
            }
            if (ecIds.length() > 0) {
                this.setOCFormDataDNs(data, ecIds, oidPoses);
            } else {
                logger.debug("OdmExtractDAO.setOCFormDataDNs wasn't called because of empty ecIds");
            }
        }
    }


    protected void setStudyParemeterConfig(Study study) {
        StudyParameterValueBean param = new StudyParameterValueDAO(this.ds).findByHandleAndStudy(study.getStudyId(), "collectDob");
        study.setCollectDob(param.getValue());
    }

    protected HashMap<String, Integer> getItemGroupOIDPos(MetaDataVersionBean metadata) {
        HashMap<String, Integer> igPoses = new HashMap<String, Integer>();
        ArrayList<ItemGroupDefBean> igs = (ArrayList<ItemGroupDefBean>) metadata.getItemGroupDefs();
        for (int i = 0; i < igs.size(); ++i) {
            igPoses.put(igs.get(i).getOid(), i);
        }
        return igPoses;
    }

    protected HashMap<String, Integer> getItemOIDPos(MetaDataVersionBean metadata) {
        HashMap<String, Integer> itPoses = new HashMap<String, Integer>();
        ArrayList<ItemDefBean> its = (ArrayList<ItemDefBean>) metadata.getItemDefs();
        for (int i = 0; i < its.size(); ++i) {
            itPoses.put(its.get(i).getOid(), i);
        }
        return itPoses;
    }

    protected HashMap<Integer, String> getSectionLabels(String sectionIds) {
        HashMap<Integer, String> labels = new HashMap<Integer, String>();
        this.setSectionLabelsTypesExpected();
        ArrayList rows = this.select(this.getSectionLabelsSql(sectionIds));
        Iterator it = rows.iterator();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            Integer secId = (Integer) row.get("section_id");
            String label = (String) row.get("label");
            labels.put(secId, label);
        }
        return labels;
    }

    protected HashMap<Integer, String> getParentItemOIDs(String crfVersionIds) {
        HashMap<Integer, String> oids = new HashMap<Integer, String>();
        this.setParentItemOIDsTypesExpected();
        ArrayList rows = this.select(this.getParentItemOIDsSql(crfVersionIds));
        Iterator it = rows.iterator();
        while (it.hasNext()) {
            HashMap row = (HashMap) it.next();
            Integer itId = (Integer) row.get("item_id");
            String oid = (String) row.get("oc_oid");
            oids.put(itId, oid);
        }
        return oids;
    }

    protected String getStudyGroupClassSql(int studyId) {
        return "select sgc.study_group_class_id, sgc.name as sgc_name, gct.name as sgc_type, sgc.status_id,"
                + " sgc.subject_assignment, sg.study_group_id, sg.name as sg_name, sg.description from study_group_class sgc,"
                + " study_group sg, group_class_types gct where study_id in (" + studyId + ")" + " and sgc.study_group_class_id = sg.study_group_class_id"
                + " and sgc.group_class_type_id = gct.group_class_type_id order by sgc.study_group_class_id";
    }

    protected String getStudyEventAndFormMetaSql(int parentStudyId, int studyId, boolean isIncludedSite, String[]
            showArchived) {
        return "select sed.ordinal as definition_order, edc.ordinal as crf_order, edc.crf_id, cv.crf_version_id,"
                + " sed.oc_oid as definition_oid, sed.name as definition_name, sed.repeating as definition_repeating,"
                + " sed.type as definition_type, cv.oc_oid as cv_oid,"
                + " cv.name as cv_name, edc.required_crf as cv_required, edc.null_values, crf.name as crf_name,crf.oc_oid as crf_oid ,sed.status_id as sed_status_id,edc.status_id as edc_status_id"
                + " from " + this.studyEventAndFormMetaTables() + this.studyEventAndFormMetaCondition(parentStudyId, studyId, isIncludedSite, showArchived);
    }

    protected String getStudyEventAndFormMetaOC1_3Sql(int parentStudyId, int studyId,
                                                      boolean isIncludedSite, String[] showArchived) {
        return "select sed.ordinal as definition_order, edc.ordinal as crf_order, edc.crf_id, cv.crf_version_id,"
                + " sed.oc_oid as definition_oid, cv.oc_oid as cv_oid,"
                + " sed.description, sed.category, cv.description as version_description, cv.revision_notes,"
                + " crf.oc_oid as crf_oid, crf.description as crf_description, edc.null_values, edc.default_version_id, edc.electronic_signature,"
                + " edc.double_entry, edc.hide_crf, edc.participant_form,edc.allow_anonymous_submission,edc.submission_url,case when edc_tag.active is null then false else edc_tag.active end, edc.source_data_verification_code,sed.status_id as sed_status_id,edc.status_id as edc_status_id"
                + " from " + this.studyEventAndFormMetaTables() + this.studyEventAndFormMetaCondition(parentStudyId, studyId, isIncludedSite, showArchived);
    }

    protected String studyEventAndFormMetaTables() {
        // return "study_event_definition sed, event_definition_crf edc, crf, crf_version cv ";
        return "event_definition_crf edc Join crf crf on crf.crf_id = edc.crf_id left outer Join event_definition_crf_permission_tag pt on pt.event_definition_crf_id = edc.event_definition_crf_id "
                + " Join crf_version cv on cv.crf_id=crf.crf_id Join study_event_definition sed on sed.study_event_definition_id = edc.study_event_definition_id"
                + "  left Join  event_definition_crf_tag edc_tag on edc_tag.path::text = ((sed.oc_oid::text || '.'::text) || crf.oc_oid::text)";
    }

    protected String studyEventAndFormMetaCondition(int parentStudyId, int studyId,
                                                    boolean isIncludedSite, String[] showArchived) {
        return " where sed.study_id = " + parentStudyId + " " + showArchived[0] + " and "
                + this.getEventDefinitionCrfCondition(studyId, parentStudyId, isIncludedSite) + " " + showArchived[1] + " and edc.crf_id = crf.crf_id "
                + showArchived[2] + " and crf.crf_id = cv.crf_id  " + showArchived[3]
                + " order by sed.ordinal, edc.ordinal, edc.crf_id, cv.crf_version_id desc";
    }


    protected String getItemDataMaxLengths(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return "select item_id, max(length(value)) as max_length from item_data where item_id in ("
                + " select distinct ifm.item_id from item_form_metadata ifm where ifm.crf_version_id in (" + crfVersionIds
                + ")) and length(value) > 0 group by item_id";
    }

    protected String getItemGroupAndItemMetaSql(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return "select cv.crf_id, cv.crf_version_id,"
                + " ig.item_group_id, item.item_id, rs.response_set_id, cv.oc_oid as crf_version_oid, ig.oc_oid as item_group_oid, item.oc_oid as item_oid,"
                + " ig.name as item_group_name, item.name as item_name, item.item_data_type_id, ifm.item_header, ifm.left_item_text,"
                + " ifm.right_item_text, ifm.required as item_required, ifm.regexp, ifm.regexp_error_msg, ifm.width_decimal,"
                + " rs.response_type_id, rs.options_text, rs.options_values, rs.label as response_label,"
                + " igm.item_group_header, igm.repeating_group,item.description as item_description, item.brief_description as item_brief_description, ifm.section_id, ifm.question_number_label from crf_version cv,"
                + " (select crf_version_id, item_id, response_set_id, header as item_header, left_item_text, right_item_text, required, regexp,"
                + " regexp_error_msg, width_decimal, section_id, question_number_label from item_form_metadata where crf_version_id in (" + crfVersionIds
                + "))ifm, item, response_set rs,"
                + " (select crf_version_id, item_group_id, item_id, header as item_group_header,repeating_group from item_group_metadata where crf_version_id in ("
                + crfVersionIds + "))igm," + " item_group ig " + this.getItemGroupAndItemMetaCondition(crfVersionIds);
    }

    protected String getItemGroupAndItemMetaOC1_3Sql(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return "select cv.crf_id, cv.crf_version_id,"
                + " ig.item_group_id, item.item_id, rs.response_set_id, cv.oc_oid as crf_version_oid, ig.oc_oid as item_group_oid, item.oc_oid as item_oid,"
                + " ifm.item_header, ifm.subheader, ifm.section_id, ifm.left_item_text, ifm.right_item_text,"
                + " ifm.parent_id, ifm.column_number, ifm.page_number_label, ifm.response_layout, ifm.default_value, item.phi_status, ifm.show_item, "
                + " rs.response_type_id, igm.repeat_number, igm.repeat_max, igm.show_group,orderInForm.item_order,igm.item_group_header from crf_version cv, (select crf_version_id, item_id, response_set_id,"
                + " header as item_header, subheader, section_id, left_item_text, right_item_text,"
                + " parent_id, column_number, page_number_label, response_layout,"
                + " default_value, show_item from item_form_metadata where crf_version_id in (" + crfVersionIds + "))ifm, item, response_set rs,"
                + " (select crf_version_id, item_group_id, item_id, header as item_group_header,"
                + " repeat_number, repeat_max, show_group from item_group_metadata where crf_version_id in (" + crfVersionIds + "))igm," + " item_group ig, "
                + this.getOrderInForm(crfVersionIds) + this.getItemGroupAndItemMetaConditionalOrderItems(crfVersionIds);
    }

    /**
     * The form display is determined the way items are ordered in CRF and this information is obtained from OID and
     * nothing hence this approach
     * @param crfVersionIds
     * @return
     */
    protected String getOrderInForm(String crfVersionIds) {
        return "(select ordinal  as item_order,crf_version_id, item_id from item_form_metadata ifmd )orderInForm";
    }

    protected String getItemGroupAndItemMetaCondition(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return " where cv.crf_version_id in (" + crfVersionIds + ") and cv.crf_version_id = ifm.crf_version_id"
                + " and ifm.item_id = item.item_id and ifm.response_set_id = rs.response_set_id"
                + " and ifm.item_id = igm.item_id and cv.crf_version_id = igm.crf_version_id and igm.item_group_id = ig.item_group_id"
                + " order by cv.crf_id, cv.crf_version_id desc, ig.item_group_id, item.item_id, rs.response_set_id";
    }

    protected String getItemGroupAndItemMetaConditionalOrderItems(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return " where cv.crf_version_id in (" + crfVersionIds + ") and cv.crf_version_id = ifm.crf_version_id"
                + " and ifm.item_id = item.item_id and ifm.response_set_id = rs.response_set_id"
                + " and ifm.item_id = igm.item_id and cv.crf_version_id = igm.crf_version_id and igm.item_group_id = ig.item_group_id"
                + " and orderInForm.crf_version_id = cv.crf_version_id and orderInForm.item_id = ifm.item_id"

                + " order by cv.crf_id, cv.crf_version_id desc, ig.item_group_id, item.item_id, rs.response_set_id";
    }

    protected String getSubjectEventFormSql(String studyIds, String sedIds, String itemIds, String dateConstraint,
                                            int datasetItemStatusId) {
        return "select ss.oc_oid as study_subject_oid, sed.ordinal as definition_order, sed.oc_oid as definition_oid,"
                + " sed.repeating as definition_repeating, se.sample_ordinal as sample_ordinal, edc.ordinal as crf_order, "
                + " cv.oc_oid as crf_version_oid, ec.event_crf_id from (select study_event_id, study_event_definition_id, study_subject_id,"
                + " sample_ordinal from study_event where study_event_definition_id in " + sedIds
                + " and study_subject_id in (select ss.study_subject_id from study_subject ss where ss.study_id in (" + studyIds + ") " + dateConstraint
                + ")) se, (select ss.oc_oid, ss.study_subject_id from study_subject ss where ss.study_id in (" + studyIds + ") " + dateConstraint + ") ss,"
                + " study_event_definition sed, event_definition_crf edc,"
                + " (select event_crf_id, crf_version_id, study_event_id from event_crf where event_crf_id in ("
                + getEventCrfIdsByItemDataSql(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId) + ")) ec, crf_version cv"
                + " where sed.study_event_definition_id in " + sedIds
                + " and sed.study_event_definition_id = se.study_event_definition_id and se.study_subject_id = ss.study_subject_id"
                + " and sed.study_event_definition_id = edc.study_event_definition_id and se.study_event_id = ec.study_event_id"
                + " and edc.crf_id = cv.crf_id and ec.crf_version_id = cv.crf_version_id order by ss.oc_oid, sed.ordinal, se.sample_ordinal, edc.ordinal";
    }

    protected String getSubjectEventFormSqlSS(String studyIds, String sedIds, String itemIds, String dateConstraint,
                                              int datasetItemStatusId,
                                              String studySubjectIds) {
        return "select ss.oc_oid as study_subject_oid, sed.ordinal as definition_order, sed.oc_oid as definition_oid,"
                + " sed.repeating as definition_repeating, se.sample_ordinal as sample_ordinal, edc.ordinal as crf_order, "
                + " cv.oc_oid as crf_version_oid, ec.event_crf_id from (select study_event_id, study_event_definition_id, study_subject_id,"
                + " sample_ordinal from study_event where study_event_definition_id in " + sedIds + " and study_subject_id in ( " + studySubjectIds
                + ")) se, (select ss.oc_oid, ss.study_subject_id from study_subject ss where ss.study_subject_id in (" + studySubjectIds + ") " + ") ss,"
                + " study_event_definition sed, event_definition_crf edc,"
                + " (select event_crf_id, crf_version_id, study_event_id from event_crf where event_crf_id in ("
                + getEventCrfIdsByItemDataSqlSS(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId, studySubjectIds) + ")) ec, crf_version cv"
                + " where sed.study_event_definition_id in " + sedIds
                + " and sed.study_event_definition_id = se.study_event_definition_id and se.study_subject_id = ss.study_subject_id"
                + " and sed.study_event_definition_id = edc.study_event_definition_id and se.study_event_id = ec.study_event_id"
                + " and edc.crf_id = cv.crf_id and ec.crf_version_id = cv.crf_version_id "
                + "order by ss.oc_oid, sed.ordinal, se.sample_ordinal, edc.ordinal";
    }

    protected String getOCSubjectEventFormSql(String studyIds, String sedIds, String itemIds, String dateConstraint,
                                              int datasetItemStatusId) {
        return "select ss.oc_oid as study_subject_oid, ss.label, ss.unique_identifier, ss.secondary_label, ss.gender, ss.date_of_birth,"
                + " ss.status_id, ss.sgc_id, ss.sgc_name, ss.sg_name, sed.ordinal as definition_order, sed.oc_oid as definition_oid, sed.repeating as definition_repeating,"
                + " se.sample_ordinal as sample_ordinal, se.se_location, se.date_start, se.date_end, se.start_time_flag,"
                + " se.end_time_flag, se.workflow_status as event_workflow_status, edc.ordinal as crf_order,"
                + " cv.oc_oid as crf_version_oid, cv.name as crf_version, cv.status_id as cv_status_id, ec.workflow_status as event_crf_workflow_status , ec.event_crf_id, ec.date_interviewed,"
                + " ec.interviewer_name, ec.validator_id from (select study_event_id, study_event_definition_id, study_subject_id, location as se_location,"
                + " sample_ordinal, date_start, date_end, workflow_status, start_time_flag, end_time_flag from study_event "
                + " where study_event_definition_id in " + sedIds
                + " and study_subject_id in (select ss.study_subject_id from study_subject ss where ss.study_id in (" + studyIds + ") " + dateConstraint
                + ")) se, ( select st_sub.oc_oid, st_sub.study_subject_id, st_sub.label,"
                + " st_sub.secondary_label, st_sub.subject_id, st_sub.unique_identifier, st_sub.gender, st_sub.date_of_birth, st_sub.status_id,"
                + " sb_g.sgc_id, sb_g.sgc_name, sb_g.sg_id, sb_g.sg_name from (select ss.oc_oid, ss.study_subject_id, ss.label, ss.secondary_label, ss.subject_id,"
                + " s.unique_identifier, s.gender, s.date_of_birth, s.status_id from study_subject ss, subject s where ss.study_id in (" + studyIds + ") "
                + dateConstraint
                + " and ss.subject_id = s.subject_id)st_sub left join (select sgm.study_subject_id, sgc.study_group_class_id as sgc_id, sgc.name as sgc_name,"
                + " sg.study_group_id as sg_id, sg.name as sg_name from subject_group_map sgm, study_group_class sgc, study_group sg where sgc.study_id in ("
                + studyIds + ") and sgm.study_group_class_id = sgc.study_group_class_id and sgc.study_group_class_id = sg.study_group_class_id"
                + " and sgm.study_group_id = sg.study_group_id) sb_g on st_sub.study_subject_id = sb_g.study_subject_id) ss, "
                + " study_event_definition sed, event_definition_crf edc,"
                + " (select event_crf_id, crf_version_id, study_event_id, status_id, date_interviewed, interviewer_name, validator_id from event_crf where event_crf_id in ("
                + getEventCrfIdsByItemDataSql(studyIds, sedIds, itemIds, dateConstraint, datasetItemStatusId) + ")) ec, crf_version cv"
                + " where sed.study_event_definition_id in " + sedIds
                + " and sed.study_event_definition_id = se.study_event_definition_id and se.study_subject_id = ss.study_subject_id"
                + " and sed.study_event_definition_id = edc.study_event_definition_id and se.study_event_id = ec.study_event_id"
                + " and edc.crf_id = cv.crf_id and ec.crf_version_id = cv.crf_version_id order by ss.oc_oid, sed.ordinal, se.sample_ordinal, edc.ordinal, ss.sgc_id";
    }


    protected String getOCSubjectEventFormSqlSS(String studyIds, String sedIds, String itemIds, String
            dateConstraint, int datasetItemStatusId,
                                                String studySubjectIds) {
        String ecStatusConstraint = getECStatusConstraint(datasetItemStatusId);
        return "select Distinct ss.oc_oid as\n" +
                "        study_subject_oid, ss.label, s.unique_identifier, ss.secondary_label, s.gender, s.date_of_birth, ss.status_id, null as sgc_id, null as sgc_name, null as sg_name,  sed.ordinal\n" +
                "        as definition_order, sed.oc_oid as definition_oid, sed.repeating as definition_repeating, se.sample_ordinal as\n" +
                "        sample_ordinal, se.location as se_location, se.date_start, se.date_end, se.start_time_flag, se.end_time_flag, se.workflow_status \n" +
                "        as event_workflow_status,se.removed as event_removed ,se.archived as event_archived,se.locked as event_locked,se.signed as event_signed, edc.ordinal as crf_order, edc.event_definition_crf_id as edc_id, fl.oc_oid as\n" +
                "        form_layout_oid, fl.name as form_layout_name, fl.status_id as fl_status_id, ec.workflow_status as event_crf_workflow_status,ec.removed as form_removed,ec.archived as form_archived, ec.\n" +
                "        event_crf_id, ec.date_interviewed, ec.interviewer_name, ec.validator_id, ec.sdv_status , sed.name as definition_name\n" +
                "        from\n" +
                "        study_event se,\n" +
                "        study_event_definition sed,\n" +
                "        event_definition_crf edc,\n" +
                "        study_subject ss,\n" +
                "        subject s,\n" +
                "        event_crf ec,\n" +
                "        form_layout fl,\n" +
                "        item_data idata\n" +
                "        where\n" +
                "        s.subject_id = ss.subject_id\n" +
                "        and ec.form_layout_id = fl.form_layout_id\n" +
                "        and idata.event_crf_id = ec.event_crf_id\n" +
                "        and ec.study_subject_id = ss.study_subject_id\n" +
                "        and ec.study_event_id = se.study_event_id\n" +
                "        and se.study_event_definition_id = sed.study_event_definition_id\n" +
                "        and edc.study_event_definition_id = sed.study_event_definition_id\n" +
                "        and edc.crf_id = fl.crf_id\n" +
                "        and sed.study_event_definition_id in " + sedIds + " \n" +
                "        and ss.study_subject_id in \n" +
                "        (" + studySubjectIds + ")\n" +
                "        and idata.item_id in " + itemIds +
                "        and ec.workflow_status " + ecStatusConstraint +
                "        and (ec.removed  !='true' or ec.removed  IS NULL) " +
                "        and (ec.archived !='true' or ec.archived IS NULL) " +
                "        and (se.removed  !='true' or se.removed  IS NULL) " +
                "        and (se.archived !='true' or se.archived IS NULL) " +
                "        order by ss.oc_oid, sed.ordinal, se.sample_ordinal, edc.ordinal";

    }


    protected String getEventGroupItemSql(String studyIds, String sedIds, String itemIds, String dateConstraint,
                                          int datasetItemStatusId) {
        String ecStatusConstraint = this.getECStatusConstraint(datasetItemStatusId);
        return "select cvidata.event_crf_id, ig.item_group_id, ig.oc_oid as item_group_oid, ig.name as item_group_name,"
                + " cvidata.item_id, cvidata.item_oid, cvidata.item_data_ordinal, cvidata.value, cvidata.item_data_type_id, cvidata.item_data_id"
                + " from (select ec.event_crf_id, ec.crf_version_id, item.item_id, item.oc_oid as item_oid,"
                + " idata.ordinal as item_data_ordinal, idata.value as value, item.item_data_type_id, idata.item_data_id from item,"
                + " (select event_crf_id, item_id, ordinal, item_data_id, value from item_data where "
                + "  event_crf_id in (select distinct event_crf_id from event_crf where study_subject_id in (select distinct"
                + " ss.study_subject_id from study_subject ss where ss.study_id in (" + studyIds + ") " + dateConstraint + ") and study_event_id"
                + " in (select distinct study_event_id from study_event" + " where study_event_definition_id in " + sedIds + " and study_subject_id in ("
                + " select distinct ss.study_subject_id from study_subject ss where ss.study_id in (" + studyIds + ") " + dateConstraint + "))))idata,"
                + " (select event_crf_id, crf_version_id from event_crf ec where ec.workflow_status " + ecStatusConstraint + ")ec" + " where item.item_id in " + itemIds
                + " and length(idata.value) > 0 and item.item_id = idata.item_id and idata.event_crf_id = ec.event_crf_id"
                + " order by ec.event_crf_id, ec.crf_version_id, item.item_id, idata.ordinal) cvidata, item_group_metadata igm,"
                + " item_group ig where cvidata.crf_version_id = igm.crf_version_id and cvidata.item_id = igm.item_id"
                + " and igm.item_group_id = ig.item_group_id order by cvidata.event_crf_id, ig.item_group_id, cvidata.item_id, cvidata.item_data_ordinal";
    }

    // should mapped to non-completed
    protected String getEventCrfIdsByItemDataSql(String studyIds, String sedIds, String itemIds, String
            dateConstraint, int datasetItemStatusId) {
        String ecStatusConstraint = getECStatusConstraint(datasetItemStatusId);
        return "select distinct idata.event_crf_id from item_data idata" + " where idata.item_id in " + itemIds +
                " and idata.event_crf_id in (select event_crf_id from event_crf ec where study_subject_id in"
                + " (select ss.study_subject_id from study_subject ss WHERE ss.study_id in (" + studyIds + ") " + dateConstraint + ")"
                + " and study_event_id in (select study_event_id from study_event where study_event_definition_id in " + sedIds
                + " and study_subject_id in (select ss.study_subject_id from study_subject ss where ss.study_id in (" + studyIds + ") " + dateConstraint + "))"
                + " and (ec.workflow_status " + ecStatusConstraint + ")) and length(value) > 0";
    }

    protected String getEventCrfIdsByItemDataSqlSS(String studyIds, String sedIds, String itemIds, String
            dateConstraint, int datasetItemStatusId,
                                                   String studySubjectIds) {
        String ecStatusConstraint = getECStatusConstraint(datasetItemStatusId);
        return "select distinct idata.event_crf_id from item_data idata" + " where idata.item_id in " + itemIds +
                " and idata.event_crf_id in (select event_crf_id ec from event_crf where study_subject_id in"
                + " (select ss.study_subject_id from study_subject ss WHERE ss.study_subject_id in (" + studySubjectIds + ") " + ")"
                + " and study_event_id in (select study_event_id from study_event where study_event_definition_id in " + sedIds
                + " and study_subject_id in (select ss.study_subject_id from study_subject ss where ss.study_subject_id in (" + studySubjectIds + ") " + "))"
                + " and (ec.workflow_status " + ecStatusConstraint + ")) and length(value) > 0";
    }

    protected String getEventDefinitionCrfCondition(int studyId, int parentStudyId, boolean isIncludedSite) {
        if (isIncludedSite) {
            return "edc.study_id = " + studyId;
        } else {
            if (studyId == parentStudyId) {
                return "edc.study_id = " + studyId;
            } else {
                return "((edc.study_id = " + parentStudyId + " or edc.study_id = " + studyId + ") and edc.event_definition_crf_id not in "
                        + "(select parent_id from event_definition_crf e where e.study_id = " + studyId + "))";
            }
        }
    }

    protected String getStudyMeasurementUnitsSql(int studyId) {
        return "select distinct mu.oc_oid as mu_oid, mu.name from event_definition_crf edc, crf_version cv, versioning_map vm, item, measurement_unit mu"
                + " where edc.study_id =" + studyId + " and edc.crf_id = cv.crf_id"
                + " and cv.crf_version_id = vm.crf_version_id and vm.item_id = item.item_id " + " and item.units = mu.name order by mu.oc_oid";
    }

    protected String getStudyMeasurementUnitsSql(String crfVersionOid) {
        return "select distinct mu.oc_oid as mu_oid, mu.name from  crf_version cv, versioning_map vm, item, measurement_unit mu " + "where cv.oc_OID in (\'"
                + crfVersionOid + "\')   and cv.crf_version_id = vm.crf_version_id and vm.item_id = item.item_id "
                + "and item.units = mu.name order by mu.oc_oid";
    }

    protected String getEventGroupItemWithUnitSql(String studyIds, String sedIds, String itemIds, String
            dateConstraint, int datasetItemStatusId,
                                                  String studySubjectIds) {
        String ecStatusConstraint = this.getECStatusConstraint(datasetItemStatusId);
        return "select table1.*, mu.oc_oid as mu_oid \n" +
                "from       (select \n" +
                "       ec.event_crf_id, ig.item_group_id, ig.oc_oid as item_group_oid, ig.name as item_group_name, \n" +
                "item.item_id as item_id, item.oc_oid as item_oid, idata.ordinal  as item_data_ordinal, idata.value, item.item_data_type_id, \n" +
                "idata.item_data_id , item.name as item_name ,ec.form_layout_id , item.units \n" +
                "      \n" +
                "      \n" +
                "       from \n" +
                "      item , item_data idata, event_crf ec ,study_subject ss,study_event se,item_group_metadata igm, item_group ig \n" +
                "      \n" +
                "      where \n" +
                "      idata.item_id=item.item_id and \n" +
                "      igm.item_id= item.item_id and\n" +
                "      igm.item_group_id= ig.item_group_id and\n" +
                "      ec.event_crf_id =idata.event_crf_id and\n" +
                "      ec.study_event_id=se.study_event_id and \n" +
                "      ec.study_subject_id = ss.study_subject_id and\n" +
                "      \n" +
                "       ss.study_subject_id in (" + studySubjectIds + ") and \n" +
                "\n" +
                "      item.item_id in " + itemIds + " and\n" +
                "      ec.workflow_status " + ecStatusConstraint + " and\n" +
                "      se.study_event_definition_id in  " + sedIds +
                "      ) table1\n" +
                "       left join\n" +
                "      measurement_unit mu on      table1.units = mu.name\n" +
                "ORDER BY table1.event_crf_id, table1.item_group_id, table1.item_id, table1.item_data_ordinal";
    }


    protected String getItemGroupAndItemMetaWithUnitSql(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return "select cv.*, mu.oc_oid as mu_oid from (" + this.getItemGroupAndItemMetaSql(crfVersionIds) + ")cv left join"
                + " (select item.item_id, mu.oc_oid from item, measurement_unit mu where item.item_id in (select vm.item_id from versioning_map vm"
                + " where vm.crf_version_id in (" + crfVersionIds + "))" + " and item.units = mu.name )mu on cv.item_id = mu.item_id ";
    }

    protected String getNullValueCVsSql(String studyId) {
        return "select sed.oc_oid as definition_oid, cv.oc_oid as crf_version_oid, edc.null_values from study_event_definition sed, event_definition_crf edc, crf_version cv"
                + " where edc.study_id = " + studyId + " and length(edc.null_values) > 0" + " and sed.study_event_definition_id = edc.study_event_definition_id"
                + " and edc.crf_id = cv.crf_id";
    }

    protected String getStudyUsersSql(String studyId) {
        return "select distinct ua.user_id, ua.first_name, ua.last_name, ua.institutional_affiliation" + " from user_account ua, study_user_role sur"
                + " where sur.study_id = " + studyId + " and sur.user_name = ua.user_name and ua.user_type_id !=4 order by ua.user_id";
    }

    protected String getOCSubjectDataAuditsSql(String studySubjectOids) {
        return "(select ss.oc_oid as study_subject_oid, ale.audit_id, alet.name, ale.user_id,"
                + " ale.audit_date, ale.reason_for_change, ale.old_value, ale.new_value, ale.audit_log_event_type_id"
                + " from audit_log_event ale, study_subject ss, audit_log_event_type alet" + " where audit_table = 'subject' and ss.oc_oid in ("
                + studySubjectOids + ") and ss.subject_id = ale.entity_id" + " and ale.audit_log_event_type_id = alet.audit_log_event_type_id" + " ) union "
                + " (select ss.oc_oid as study_subject_oid, ale.audit_id,  alet.name, ale.user_id,"
                + " ale.audit_date, ale.reason_for_change, ale.old_value, ale.new_value, ale.audit_log_event_type_id"
                + " from audit_log_event ale, study_subject ss, audit_log_event_type alet" + " where audit_table = 'study_subject' and ss.oc_oid in ("
                + studySubjectOids + ") and ss.study_subject_id = ale.entity_id" + " and ale.audit_log_event_type_id = alet.audit_log_event_type_id"
                + " ) union " + " (select ss.oc_oid as study_subject_oid, ale.audit_id, alet.name, ale.user_id,"
                + " ale.audit_date, ale.reason_for_change, ale.old_value, ale.new_value, ale.audit_log_event_type_id"
                + " from audit_log_event ale, study_subject ss, subject_group_map sgm, audit_log_event_type alet"
                + " where audit_table = 'subject_group_map' and ss.oc_oid in (" + studySubjectOids + ") and ss.study_subject_id = sgm.study_subject_id"
                + " and ale.entity_id = sgm.subject_group_map_id and ale.audit_log_event_type_id = alet.audit_log_event_type_id"
                + " ) order by study_subject_oid, audit_id asc";
    }

    protected String getOCEventDataAuditsSql(String studySubjectOids) {
        return "select ss.oc_oid as study_subject_oid, sed.oc_oid as definition_oid, ale.audit_id,se.sample_ordinal as event_repeat,"
                + " alet.name, ale.user_id, ale.audit_date,ale.entity_name ,ale.reason_for_change, ale.old_value, ale.new_value, ale.details," + " ale.audit_log_event_type_id"
                + " from audit_log_event ale, study_subject ss, study_event se, study_event_definition sed, audit_log_event_type alet"
                + " where audit_table = 'study_event' and ss.oc_oid in (" + studySubjectOids + ") and ss.study_subject_id = se.study_subject_id"
                + " and ale.entity_id = se.study_event_id" + " and se.study_event_definition_id = sed.study_event_definition_id"
                + " and ale.audit_log_event_type_id = alet.audit_log_event_type_id" + " order by ss.oc_oid, sed.oc_oid, ale.audit_id asc";
    }

    protected String getOCFormDataAuditsSql(String studySubjectOids, String ecIds) {
        return "select ale.entity_id as event_crf_id, ale.audit_id, alet.name, ale.user_id,"
                + " ale.audit_date,ale.entity_name, ale.reason_for_change, ale.old_value, ale.new_value, ale.audit_log_event_type_id"
                + " from audit_log_event ale, study_subject ss, event_crf ec, audit_log_event_type alet"
                + " where audit_table = 'event_crf' and ec.event_crf_id in (" + ecIds + ") and ss.oc_oid in (" + studySubjectOids
                + ") and ss.study_subject_id = ec.study_subject_id" + " and ale.entity_id = ec.event_crf_id"
                + " and ale.audit_log_event_type_id = alet.audit_log_event_type_id" + " order by ale.audit_id asc";
    }

    protected String getOCItemDataAuditsSql(String idataIds) {
        return "select ale.entity_id as item_data_id, ale.audit_id, alet.name, ale.user_id,"
                + " ale.audit_date, ale.reason_for_change, ale.old_value, ale.new_value, ale.audit_log_event_type_id"
                + " from audit_log_event ale, audit_log_event_type alet" + " where audit_table = 'item_data' and ale.entity_id in (" + idataIds
                + ") and ale.audit_log_event_type_id = alet.audit_log_event_type_id" + " order by ale.entity_id asc";
    }

    protected String getOCSubjectDataDNsSql(String studySubjectOids) {
        return "(select ss.oc_oid as study_subject_oid, dn.parent_dn_id, dn.discrepancy_note_id as dn_id, dn.description, dn.detailed_notes, "
                + " dn.owner_id, dn.date_created, rs.name as status, dnt.name , dn.thread_uuid, dn.thread_number "
                + " from discrepancy_note dn, dn_subject_map dnsm, study_subject ss, discrepancy_note_type dnt, resolution_status rs"
                + " where dn.entity_type = 'subject'" + " and dn.discrepancy_note_id = dnsm.discrepancy_note_id and ss.oc_oid in (" + studySubjectOids
                + ") and ss.subject_id = dnsm.subject_id and dn.resolution_status_id = rs.resolution_status_id"
                + " and dn.discrepancy_note_type_id = dnt.discrepancy_note_type_id) union"
                + "(select ss.oc_oid as study_subject_oid, dn.parent_dn_id, dn.discrepancy_note_id as dn_id, dn.description, dn.detailed_notes, "
                + " dn.owner_id, dn.date_created, rs.name as status, dnt.name , dn.thread_uuid, dn.thread_number "
                + " from discrepancy_note dn, dn_study_subject_map dnssm, study_subject ss, discrepancy_note_type dnt, resolution_status rs"
                + " where dn.entity_type = 'studySub'" + " and dn.discrepancy_note_id = dnssm.discrepancy_note_id and ss.oc_oid in (" + studySubjectOids
                + ") and ss.study_subject_id = dnssm.study_subject_id and dn.resolution_status_id = rs.resolution_status_id"
                + " and dn.discrepancy_note_type_id = dnt.discrepancy_note_type_id" + ") order by study_subject_oid, parent_dn_id, dn_id";
    }

    protected String getOCEventDataDNsSql(String definitionOids, String studySubjectOids) {
        return "select ss.oc_oid as study_subject_oid, sed.oc_oid as definition_oid, dn.parent_dn_id, dn.discrepancy_note_id as dn_id, dn.description, dn.detailed_notes, "
                + " dn.owner_id, dn.date_created, rs.name as status, dnt.name , dn.thread_uuid, dn.thread_number"
                + " from discrepancy_note dn, dn_study_event_map dnsem, study_event se, study_event_definition sed, study_subject ss, discrepancy_note_type dnt, resolution_status rs"
                + " where dn.entity_type = 'studyEvent'"
                + " and dn.discrepancy_note_id = dnsem.discrepancy_note_id and dnsem.study_event_id = se.study_event_id" + " and sed.oc_oid in ("
                + definitionOids + ") and se.study_event_definition_id = sed.study_event_definition_id" + " and ss.oc_oid in (" + studySubjectOids
                + ") and se.study_subject_id = ss.study_subject_id" + " and dn.resolution_status_id = rs.resolution_status_id"
                + " and dn.discrepancy_note_type_id = dnt.discrepancy_note_type_id"
                + " order by ss.oc_oid, sed.oc_oid, dn.parent_dn_id, dn.discrepancy_note_id";
    }

    protected String getOCFormDataDNsSql(String ecIds) {
        return "select ec.event_crf_id, dn.parent_dn_id, dn.discrepancy_note_id as dn_id, dn.description, dn.detailed_notes, dn.owner_id, dn.date_created, rs.name as status, dnt.name , dn.thread_uuid, dn.thread_number"
                + " from discrepancy_note dn, dn_event_crf_map dnecm, event_crf ec, discrepancy_note_type dnt, resolution_status rs"
                + " where dn.entity_type = 'eventCrf'" + " and dnecm.event_crf_id in (" + ecIds + ") and dn.discrepancy_note_id = dnecm.discrepancy_note_id"
                + " and ec.event_crf_id in (" + ecIds + ") and dnecm.event_crf_id = ec.event_crf_id" + " and dn.resolution_status_id = rs.resolution_status_id"
                + " and dn.discrepancy_note_type_id = dnt.discrepancy_note_type_id" + " order by ec.event_crf_id, dn.parent_dn_id, dn.discrepancy_note_id";
    }

    protected String getOCItemDataDNsSql(String idataIds) {
        return "select dnidm.item_data_id, dn.parent_dn_id, dn.discrepancy_note_id as dn_id, dn.description, dn.detailed_notes, dn.owner_id, dn.date_created, rs.name as status, dnt.name, dn.thread_uuid, dn.thread_number"
                + " from discrepancy_note dn, dn_item_data_map dnidm, discrepancy_note_type dnt, resolution_status rs"
                + " where (dn.entity_type = 'itemData' or dn.entity_type = 'itemdata')" + " and dnidm.item_data_id in (" + idataIds
                + ") and dn.discrepancy_note_id = dnidm.discrepancy_note_id" + " and dn.resolution_status_id = rs.resolution_status_id"
                + " and dn.discrepancy_note_type_id = dnt.discrepancy_note_type_id" + " order by dnidm.item_data_id, dn.parent_dn_id, dn.discrepancy_note_id";
    }

    protected String getItemCVOIDsSql(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return "select cv.crf_id, cv.crf_version_id, item.item_id, cv.oc_oid as cv_oid, item.oc_oid as item_oid"
                + " from crf_version cv, versioning_map vm, item" + " where vm.crf_version_id in (" + crfVersionIds + ")"
                + " and vm.crf_version_id = cv.crf_version_id and vm.item_id = item.item_id" + " order by cv.crf_id, cv.crf_version_id desc, item.item_id";
    }

    protected String getItemCVOIDsSqlUpdated(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return "select cv.crf_id, cv.crf_version_id, item.item_id, cv.oc_oid as cv_oid, item.oc_oid as item_oid , c.oc_oid as crf_oid "
                + " from crf_version cv, versioning_map vm, crf c , item" + " where vm.crf_version_id in (" + crfVersionIds + ")"
                + " and vm.crf_version_id = cv.crf_version_id and c.crf_id = cv.crf_id and vm.item_id = item.item_id"
                + " order by cv.crf_id, cv.crf_version_id desc, item.item_id";
    }

    protected String getSectionLabelsSql(String sectionIds) {
        sectionIds = sectionIds.length() > 0 ? sectionIds : "0";
        return "select section_id, label from section where section_id in (" + sectionIds + ")";
    }

    protected String getParentItemOIDsSql(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return "select item_id, oc_oid from item where item_id in (select distinct parent_id from item_form_metadata ifm" + " where crf_version_id in ("
                + crfVersionIds + ") and ifm.parent_id > 0 )";
    }

    protected String getSCDsSql(String crfVersionIds) {
        crfVersionIds = crfVersionIds.length() > 0 ? crfVersionIds : "0";
        return "select cv.crf_id, cv.crf_version_id, item.item_id, cv.oc_oid as crf_version_oid, item.oc_oid as item_oid,"
                + " ifm.control_item_name, ifm.option_value, ifm.message from crf_version cv,"
                + " (select im.crf_version_id, im.item_id, scd.control_item_name, scd.option_value, scd.message"
                + " from item_form_metadata im, scd_item_metadata scd where im.crf_version_id in (" + crfVersionIds
                + ") and im.item_form_metadata_id = scd.scd_item_form_metadata_id)ifm, item" + " where cv.crf_version_id in (" + crfVersionIds
                + ") and cv.crf_version_id = ifm.crf_version_id" + " and ifm.item_id = item.item_id order by cv.crf_id, cv.crf_version_id desc, item.item_id";
    }

    protected String getErasedScoreItemDataIdsSql(String itemIds, String idataIds) {
        return "select idata.item_data_id from item_data idata where idata.value='<erased>'" + " and idata.item_data_id in (" + idataIds
                + ") and idata.item_id in (" + " select distinct ifm.item_id from item_form_metadata ifm where ifm.response_set_id in ("
                + " select rs.response_set_id from response_set rs where rs.response_type_id in (8,9))" + "      and ifm.item_id in " + itemIds + " limit 999)";
    }

    protected String getSectionDetails(String CrfVersionOID) {
        return "select section_id, label,title,subtitle,instructions,page_number_label from section section where crf_version_id=?";
    }

    protected String permissionTagsLookupNoPermissionTags(EventDefinitionCRFBean edc) {
        String query = "select * from event_definition_crf_permission_tag pt where pt.event_definition_crf_id=" + edc.getId() + " or pt.event_definition_crf_id=" + edc.getParentId();
        return query;
    }

    protected String permissionTagsLookupWithPermissionTags(EventDefinitionCRFBean edc, String permissionTags) {
        String query = "select * from event_definition_crf_permission_tag pt where ( pt.event_definition_crf_id=" + edc.getId() + " or pt.event_definition_crf_id=" + edc.getParentId() + ") and permission_tag_id in (" + permissionTags + ")";
        return query;
    }


    private EventDefinitionCRFBean getEventDefCRF(StudyEventDefBean studyEventDefBean, ElementRefBean
            formRef, Study studyBean) {
        StudyEventDefinitionBean sedBean = seddao.findByOid(studyEventDefBean.getOid());
        CRFBean crfBean = crfdao.findByOid(formRef.getElementDefOID());
        return edcdao.findByStudyEventDefinitionIdAndCRFId(studyBean, sedBean.getId(), crfBean.getId());
    }

    private ConfigurationParameters populateConfigurationParameters(EventDefinitionCRFBean
                                                                            edc, ConfigurationParameters conf, String crfPath) {
        conf.setAllowAnynymousSubmission(edc.isAllowAnonymousSubmission());

        conf.setOffline(getEventDefnCrfOfflineStatus(2, crfPath, true));
        conf.setHiddenCrf(edc.isHideCrf());
        conf.setParticipantForm(edc.isParticipantForm());
        conf.setSubmissionUrl(edc.getSubmissionUrl());
        return conf;
    }

    List<ElementRefBean> getFormLayoutRefs(ElementRefBean formRef, int defaultVersionId) {
        CRFBean crfBean = crfdao.findByOid(formRef.getElementDefOID());
        List<FormLayoutBean> formLayouts = fldao.findAllByCRFId(crfBean.getId());
        List<ElementRefBean> elementRefs = new ArrayList<>();
        ElementRefBean element;
        for (FormLayoutBean formLayout : formLayouts) {
            element = new ElementRefBean();
            element.setName(formLayout.getName());

            if (formLayout.getId() == defaultVersionId) {
                element.setDefaultVersion(true);
            } else {
                element.setDefaultVersion(false);
            }
            elementRefs.add(element);
        }
        return elementRefs;
    }

    List<ElementRefBean> getFormLayoutRefs(FormDefBean formDef) {
        CRFBean crfBean = crfdao.findByOid(formDef.getOid());
        List<FormLayoutBean> formLayouts = fldao.findAllByCRFId(crfBean.getId());
        List<ElementRefBean> elementRefs = new ArrayList<>();
        ElementRefBean element;
        for (FormLayoutBean formLayout : formLayouts) {
            element = new ElementRefBean();
            element.setName(formLayout.getName());
            element.setUrl(formLayout.getUrl());
            element.setStatus(core.org.akaza.openclinica.domain.Status.getByCode(formLayout.getStatusId()).getName());
            elementRefs.add(element);
        }
        return elementRefs;
    }

    public boolean getEventDefnCrfOfflineStatus(int tagId, String crfPath, boolean active) {
        EventDefinitionCrfTagBean eventDefinitionCrfTag = edctdao.findByCrfPath(tagId, crfPath, active);
        if (eventDefinitionCrfTag == null)
            return false;
        else
            return true;
    }

    private String[] showArchivedSql(boolean showArchived) {
        String[] arr = new String[4];
        if (showArchived) {
            arr[0] = "";
            arr[1] = "";
            arr[2] = "";
            arr[3] = "";
        } else {
            arr[0] = "and sed.status_id not in (5,7)";
            arr[1] = "and edc.status_id not in (5,7)";
            arr[2] = "and crf.status_id not in (5,7)";
            arr[3] = "and cv.status_id not in (5,7)";
        }
        return arr;
    }

    private boolean permissionAccessCheck(EventDefinitionCRFBean edc, String permissionTags) {
        logger.debug("Begin to permissionTagsLookup");

        ArrayList viewRows = select(permissionTagsLookupWithPermissionTags(edc, permissionTags));
        logger.info("permissionAccessCheck : "
                + permissionTagsLookupWithPermissionTags(edc, permissionTags));
        if (viewRows.size() > 0) {
            // save the edc.getId in list and return to Job.
            edcSet.add(edc.getId());
            return false;
        } else {
            ClinicalDataCollector.datasetFiltered = "YES";
            return true;
        }
    }


    private boolean isFormTagged(EventDefinitionCRFBean edc) {
        logger.debug("Begin to permissionTagsLookup");
        ArrayList viewRows = select(permissionTagsLookupNoPermissionTags(edc));
        //logger.info("permissionTagsLookup : " + permissionTagsLookupNoPermissionTags(edc));
        return (viewRows.size() > 0 ? true : false);
    }

    public String setStudyEventStatus(String value) {
        if (!StringUtils.isEmpty(value)) {
            return setStudyEventWorkflowStatus(value);
        } else {
            return "";
        }
    }

    public String setEventCrfStatus(String value) {
        if (!StringUtils.isEmpty(value)) {
            return setEventCrfWorkflowStatus(value);
        } else {
            return "";
        }
    }


    public String setOriginalEventCrfStatus(String value) {
        switch (value) {
            case "1":
                return EventCrfWorkflowStatusEnum.INITIAL_DATA_ENTRY.getDisplayValue();
            case "2":
                return EventCrfWorkflowStatusEnum.COMPLETED.getDisplayValue();
            case "5":
                return "removed";
            case "6":
                return "locked";
            case "7":
                return "removed";
            case "11":
                return EventCrfWorkflowStatusEnum.NOT_STARTED.getDisplayValue();
            default:
                return "";
        }
    }


    public String setStudyEventWorkflowStatus(String value) {
        for (StudyEventWorkflowStatusEnum theEnum : StudyEventWorkflowStatusEnum.values()) {
            if (value.equals(theEnum.toString())) {
                return theEnum.getDisplayValue();
            }
        }
        return setOriginalStudyEventStatus(value);
    }

    public String setEventCrfWorkflowStatus(String value) {
        for (EventCrfWorkflowStatusEnum theEnum : EventCrfWorkflowStatusEnum.values()) {
            if (value.equals(theEnum.toString())) {
                return theEnum.getDisplayValue();
            }
        }
        return setOriginalEventCrfStatus(value);

    }

    public String setOriginalStudyEventStatus(String value) {
        switch (value) {
            case "1":
                return StudyEventWorkflowStatusEnum.NOT_SCHEDULED.getDisplayValue();
            case "2":
                return StudyEventWorkflowStatusEnum.SCHEDULED.getDisplayValue();
            case "3":
                return StudyEventWorkflowStatusEnum.DATA_ENTRY_STARTED.getDisplayValue();
            case "4":
                return StudyEventWorkflowStatusEnum.COMPLETED.getDisplayValue();
            case "5":
                return StudyEventWorkflowStatusEnum.STOPPED.getDisplayValue();
            case "6":
                return StudyEventWorkflowStatusEnum.SKIPPED.getDisplayValue();
            case "7":
                return "locked";
            case "8":
                return "signed";
            default:
                return "";
        }
    }
}
